Avoid "Open Worksheet" dialog

[zSplash]

I am opening an Excel spreadsheet in Word, and I want to avoid the "Open
document in Workbook" dialog in which I have to select "Sheet 1." How can I
avoid getting that dialog (and just open Sheet1 automatically)? (Do I
modify the format (at Format:=wdOpenFormatAuto???)) (All my code is in
Word, and I'm only using Excel as the repository so I can work with it in
Word.)

Here's the code I'm using to open the spreadsheet in Word:
Application.DisplayAlerts = wdAlertsNone
Documents.Open FileName:="c:\4raw.xls", ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
Application.DisplayAlerts = wdAlertsAll


TIA

 

[Cindy M -WordMVP-]

Hi ZSplash,

This uses, if I'm not mistaken, the spreadsheet converter. MS has deprecated
this, and it's no longer distributed with Office installations. You should
think carefully before basing a solution on it. I don't think there's any way
to suppress this dialog box.

To what purpose are you opening the Excel workbook in Word?

I am opening an Excel spreadsheet in Word, and I want to avoid the "Open
document in Workbook" dialog in which I have to select "Sheet 1." How can I
avoid getting that dialog (and just open Sheet1 automatically)? (Do I
modify the format (at Format:=wdOpenFormatAuto???)) (All my code is in
Word, and I'm only using Excel as the repository so I can work with it in
Word.)

Here's the code I'm using to open the spreadsheet in Word:
Application.DisplayAlerts = wdAlertsNone
Documents.Open FileName:="c:\4raw.xls", ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
Application.DisplayAlerts = wdAlertsAll

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
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

 

[zSplash]

Hi, Cindy. Thanks for the help! (...had to look up deprecated -- what a
nice/diplomatic way to say it.

Purpose: I have an Excel spreadsheet which I need to use in a Word
document. I have written code to deal with the Excel spreadsheet once it's
in Word.

Sounds like you're suggesting I don't do this... What instead, please,
Cindy??

st.

 

[Jay Freedman]

I'd advise doing all manipulation of the Excel spreadsheet in Excel --
if necessary by copying it to another .xls file and manipulating that
-- rather than trying to do anything with it once it's in Word. You
can write the macro code to do the manipulations in Excel and call
that from Word VBA, or you can automate Excel from within Word VBA
(see http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm).

Get the final result in a single Excel workbook, and then use code
like this to bring it into Word:

MyRange.InlineShapes.AddOLEObject _
FileName:="C:\TempDocuments\Book1.xls"

Note that inserted objects can't cross a page boundary, so you may
have to break up the result into several workbooks and import them
onto separate pages in Word. This command also doesn't have any
flexibility to specify particular sheets or named ranges within a
workbook -- it grabs the entire used range in the active sheet.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Cindy M -WordMVP-]

Hi ZSplash,

Purpose: I have an Excel spreadsheet which I need to use in a Word
document. I have written code to deal with the Excel spreadsheet once it's
in Word.

Sounds like you're suggesting I don't do this... What instead, please,
Cindy??

I'd tend to use the "non-deprecated" approach. To see this in action, copy the
range in Excel, go to Word, then Edit/Paste Special and activate "Link".
(Don't worry if you don't want a link, that's not a problem). Now press Alt+F9
to see the field codes. You should see a LINK field?

What I do is create LINK field in the document, then break the link (if
necessary). That leaves me with a Word table, on which your code should
continue to work as it has until now

The syntax for this is, very roughly:

Dim fld As Word.Field
Dim rng As Word.Range
Dim tbl As Word.Table
Dim fldCode As String

Set rng = Selection.Range
fldCode = "LINK Excel.Sheet.8 " & Chr$(34) & "G:\\test\\test.xls" &
Chr$(34) & " " & Chr$(34) & "Sheet1!R19C3:R29C5" & Chr$(34) & " \a \f 4 \h "
Set fld = ActiveDocument.Fields.Add(Range:=rng, _
Type:=wdFieldEmpty, Text:=fldCode, _
PreserveFormatting:=False)
fld.Unlink
rng.MoveStart wdCharacter, 1
Set tbl = rng.Tables(1)

Notice how you have to "pull apart" the LINK field so that you can create the
"quotes" (Chr$(34)) in the field. You can substitute other file names or
ranges, as required.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
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

 

[Cindy M -WordMVP-]

Hi Jay,

Get the final result in a single Excel workbook, and then use code
like this to bring it into Word:

MyRange.InlineShapes.AddOLEObject _
FileName:="C:\TempDocuments\Book1.xls"

Note that inserted objects can't cross a page boundary, so you may
have to break up the result into several workbooks and import them
onto separate pages in Word.

That's why zSplash was using the technique he had: because the old
converter brings the Excel table in as a Word table...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17
2005)
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

 

[zSplash]

Thanks for the help, Cindy, as always.

Trying to follow your directions ("To see this in action, copy the range in
Excel, go to Word, then Edit/Paste Special and activate "Link"."), I can't
pasteSpecial. When I try to pasteSpecial, the link feature is grayed out.
What's the deal, with that??

TIA,A

st.

 

[Cindy M -WordMVP-]

Hi ZSplash,

Trying to follow your directions ("To see this in action, copy the range in
Excel, go to Word, then Edit/Paste Special and activate "Link"."), I can't
pasteSpecial. When I try to pasteSpecial, the link feature is grayed out.
What's the deal, with that??

Something in your current working configuration may be preventing this. You
aren't closing the Excel workbook before trying to paste, for example? Not
trying to paste into a protected document, or anything else odd? No Addins
loaded that might be interfering with linking in OLE objects?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
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