opening excel workbook

[jon]

Hi everyone, i'm trying to open an excel document in word
to form a document with a table. My problem is that when
it opens it always asks whether i want to open the entire
workbook, sheet1,2, or 3. Is there anyway of telling word
what i need (just sheet1) in vba so the user doesn't have
to make this choice?

The code i'm using is:


Documents.Open FileName:="tempdetails.xls",
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

thanks for your help.

 

[haroldk]

Try using InsertDatabase rather than opening an XL File in Word.
I recorded this macro using Word and Excel 2003. It will work in earlier
versions.
Be sure to add the path and name of the database.
Sub InsertXLTable()

Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source= Path to xls file\Title.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase" _
, SQLStatement:="SELECT * FROM `'SheetName$'`" & "",
PasswordDocument:= _
"", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", DataSource:= _
"Path to xls file\Title.xls", From:=-1 _
, To:=-1, IncludeFields:=True
End Sub

Harold

 

[Cindy M -WordMVP-]

Hi Jon,

i'm trying to open an excel document in word
to form a document with a table. My problem is that when
it opens it always asks whether i want to open the entire
workbook, sheet1,2, or 3. Is there anyway of telling word
what i need (just sheet1) in vba so the user doesn't have
to make this choice?

The easiest way is to duplicate what you get when a
spreadsheet is inserted using edit/paste special, with a
link. This generates a LINK field. Once you know the syntax
for the LINK field, it's a simple matter to use that in your
code. Roughly
Set fld = ActiveDocument.Fields.Add _
(Range:=Selection.Range, _
Type:=wdFieldEmpty, _
Text:="LINK " & Chr$(34) & "C:\\test\\sheet.xls" _
& Chr$(34) & " " & Chr$(34) & RangeName & _
Chr$(34) & " \r \ a"
PreserveFormatting:=True
'Get rid of the link
fld.Unlink

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail