Avoid "Open Worksheet" dialog

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
Tue Jul 11 06:19:09 CDT 2006

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
Tue Jul 11 12:30:39 CDT 2006

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.

"Cindy M -WordMVP-" <C.Meister-C@hispeed.ch> wrote in message
news:VA.0000c11a.006f0284@speedy...
> 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 :-)
>

Jay
Tue Jul 11 15:16:19 CDT 2006

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 FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

On Tue, 11 Jul 2006 09:30:39 -0800, "zSplash"
<zNOSPAMSplash@ReallygciPlease.net> wrote:

>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.
>
>"Cindy M -WordMVP-" <C.Meister-C@hispeed.ch> wrote in message
>news:VA.0000c11a.006f0284@speedy...
>> 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 :-)
>>
>

Cindy
Wed Jul 12 05:51:14 CDT 2006

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
Wed Jul 12 05:51:15 CDT 2006

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
Wed Jul 12 16:57:13 CDT 2006

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-" <C.Meister-C@hispeed.ch> wrote in message
news:VA.0000c13c.00c294ed@speedy...
> 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
Thu Jul 13 06:07:48 CDT 2006

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 :-)