Multiple instances of Excel and VB

[Rich]

I am using VB6 and Excel 97.

I have a problem where if the is an instance of Excel is open with an
open workbook. And I try to do OLE Automation, then the data from one
report, ends up on the spreadsheet that happened to be open at the
time.

What can I do so that when I dump data into a spreadsheet, it will not
disturb other instances of Excel.

 

[Rob Bovey]

I am using VB6 and Excel 97.

I have a problem where if the is an instance of Excel is open with an
open workbook. And I try to do OLE Automation, then the data from one
report, ends up on the spreadsheet that happened to be open at the
time.

What can I do so that when I dump data into a spreadsheet, it will not
disturb other instances of Excel.

Hi Rich,

You need to create your own separate instance of Excel and use that
instance to do whatever it is you need to do. Something along the lines of
(not tested):

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add ''' .Open for an exisiting workbook
Set xlSheet = xlBook.Worksheets(1)

''' Create your report on xlSheet. Be sure to qualify all object
''' references on xlSheet with the xlSheet variable.

xlBook.SaveAs "C:\Files\MyBook.xls" ''' just .Save for an existing workbook

Set xlSheet = Nothing
xlBook.Close False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Guest]

Keeping track of worksheet names is a good idea if your doing this with code.
Create string variables for various sheets, when you know one is opem in the
code, populate the variable with that name then you can refer to various
workbooks like

workbooks(var1).activate
sheets(1).cells.select
workbooks(var2).activate
selection.paste
(e-mail address removed)