HELP: Opening/Closing an Excel spreadsheet from Word

[jille]

Hi,

I have a fairly complex Word macro that creates an Excel file from an
existing template for use within the macro. During one session the user may
want to create multiple Excel files.

I'm using the following code to open Excel and create the new file:

Public xlbookbulk As Object

then, as a separate procedure:

With Excel.Application
Workbooks.Open vPath & "\EnBulk Information.xlt"
.Application.Visible = True
Set xlbookbulk = .ActiveWorkbook
vBulkFileName = .ActiveWorkbook.Name
End With

Everything works wonderfully until the user closes the current/first Excel
file and attempts to construct a new one. The file is constructed but it
doesn't view...you can see the Excel toolbars and the formula and status bars
but the area in the centre (where the cells would normally appear) is void,
ie the screen behind is showing through.

Now if you don't close the Excel file, you can create a new one
successfully. I'm wondering if the problem might have something to do with
Excel's status in Word...it thinks it's still open but it's not?????????

Any ideas?

Thanks,
Jille

 

[Cindy M.]

Hi Jille,

Hard to be sure without seeing all your code that declares and access the Excel
application, as well as the parts the release the objects Word is using. I'd
certainly make the following changes:

Dim xl as Excel.application
'Note I'm keeping this local, not global
'You don't indicate why you wanted a global-level object
'But unless you have a good reason, it's a bad idea
Dim xlbookbulk as Excel.Workbook

Set xl = 'Whatever you do here to get the app

With xl
Set xlbookbulk = Workbooks.Open vPath & "\EnBulk Information.xlt"
.Application.Visible = True
vBulkFileName = .ActiveWorkbook.Name
End With

'When you're code is finished, you MUST release the objects
'Otherwise you'll end up with orphaned pointers
'Excel won't be released from memory
Set xlbookbulk = Nothing
Set xl = Nothing

I have a fairly complex Word macro that creates an Excel file from an
existing template for use within the macro. During one session the user may
want to create multiple Excel files.

I'm using the following code to open Excel and create the new file:

Public xlbookbulk As Object

then, as a separate procedure:

With Excel.Application
Workbooks.Open vPath & "\EnBulk Information.xlt"
.Application.Visible = True
Set xlbookbulk = .ActiveWorkbook
vBulkFileName = .ActiveWorkbook.Name
End With

Everything works wonderfully until the user closes the current/first Excel
file and attempts to construct a new one. The file is constructed but it
doesn't view...you can see the Excel toolbars and the formula and status bars
but the area in the centre (where the cells would normally appear) is void,
ie the screen behind is showing through.

Now if you don't close the Excel file, you can create a new one
successfully. I'm wondering if the problem might have something to do with
Excel's status in Word...it thinks it's still open but it's not?????????

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