Variable Scope Question

[Ariel Dugan]

Hi,

I am working on an (excel based, VBA) application today that will be opening
multiple excel.application objects. This opening of multiple excel objects
is essential to the functionality of the application, as I will be execting
multiple adodb calls to a SQL server, and this is not possible from the same
excel instance.

What I want to accomplish is to have the worbooks opened in each of those
excel.application objects have access to a variable in the application
itself.

Do I need to declare such a type in its own class? I tried just declaring
it as public in the declarations section of a standard module in the
application.

Thanks in advance.

Ariel

 

[Bob Phillips]

Presumably, all of the Excel instances are opened in the same code thread?
Therefore a public variable applies to that thread throughout.

Give an example of the code in outline terms, as I am not seeing the problem
yet.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ariel Dugan]

Hi Bob,

Here is the code I started to write in the application I am working on.
When the new excel objects are instantiated, and the workbooks within them
are opened, the strFile variable (which I have previously added to the newly
opened workbook's code) evaluates as blank.

When each new excel workbook is opened, it opens its own instance of the vb
editor, which is what I need to occur, so does this make it a seperate code
thread.

I think I'll just have to use the registry?

Here is the code....

Option Explicit
Public strFile As String

Sub CreateWorkBook()
Dim strFile2 As String
Dim objExcel As Excel.Application
Dim objWorkbook As Workbook
strFile = ThisWorkbook.Name

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
strFile2 = "C:\Documents and Settings\ariel\My Documents\BMLP
090204.xls"
Set objWorkbook = objExcel.Workbooks.Open(strFile2)
objExcel.Visible = True
objWorkbook.Close False
Set objExcel = Nothing
Set objWorkbook = Nothing
End Sub