I have a VBA macro that uses two different excel files (workbooks) that are
open in one session of excel.

This works fine if each file is opened within one excel program. ie open
excel and then open both files via file-open within the one excel session.

If I open only one of the files in excel and then open another excel session
by clicking Start-Programs-Microsoft Office-Microsoft Offic Excel and then
use this excel session to open the other file the VBA will not run as it can
only see one of the files (workbooks).

Is there a way for excel to run a VBA between two different excel sessions?
Even if I could have the program use CopyAs to copy the workbook from one
excel session to the other.

Thanks

The word to research is "automation"...

Sub OpenInstanceOfExcel()
Dim appXL As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set appXL = New Excel.Application
Set WB = appXL.Workbooks.Open("C:\Jims Documents\Software Users List.xls")
'Set WB = appXL.Workbooks.Add
Set WS = WB.Worksheets(1)

' Do your stuff with WS

Set WS = Nothing
WB.Close SaveChanges:=False 'Your choice

Set WB = Nothing
appXL.Quit
Set appXL = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"mcphc"
wrote in message
I have a VBA macro that uses two different excel files (workbooks) that are
open in one session of excel.

This works fine if each file is opened within one excel program. ie open
excel and then open both files via file-open within the one excel session.

If I open only one of the files in excel and then open another excel session
by clicking Start-Programs-Microsoft Office-Microsoft Offic Excel and then
use this excel session to open the other file the VBA will not run as it can
only see one of the files (workbooks).

Is there a way for excel to run a VBA between two different excel sessions?
Even if I could have the program use CopyAs to copy the workbook from one
excel session to the other.

Thanks

Thanks for that Jim, but what I need is more like this:

Dim appXL As Excel.Application
Dim WB As Excel.Workbook

For Each appXL In Windows 'I don't know if this is possible
For Each WB In appXL
If appXL.WB.Name = "myExcelFile.xls" Then
appXL.WB.SaveCopyAs "C:/myExcelFileDummy.xls" 'Save copy
of file
Workbooks.Open Filename:="C:/myExcelFileDummy.xls" 'and open in
active
End If 'Excel
application
Next WB
Next appXL


I don't know if it is possible to cycle through all open Excel applications.

"Jim Cone" wrote:


The word to research is "automation"...

Sub OpenInstanceOfExcel()
Dim appXL As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set appXL = New Excel.Application
Set WB = appXL.Workbooks.Open("C:\Jims Documents\Software Users List.xls")
'Set WB = appXL.Workbooks.Add
Set WS = WB.Worksheets(1)

' Do your stuff with WS

Set WS = Nothing
WB.Close SaveChanges:=False 'Your choice

Set WB = Nothing
appXL.Quit
Set appXL = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"mcphc"
wrote in message
I have a VBA macro that uses two different excel files (workbooks) that are
open in one session of excel.

This works fine if each file is opened within one excel program. ie open
excel and then open both files via file-open within the one excel session.

If I open only one of the files in excel and then open another excel session
by clicking Start-Programs-Microsoft Office-Microsoft Offic Excel and then
use this excel session to open the other file the VBA will not run as it can
only see one of the files (workbooks).

Is there a way for excel to run a VBA between two different excel sessions?
Even if I could have the program use CopyAs to copy the workbook from one
excel session to the other.

Thanks

"I don't know if it is possible to cycle through all open Excel applications."

Not that I know of.
You could use GetObject to access an open instance of Excel, do something,
close that instance and repeat until all instances were changed/closed.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"mcphc"
wrote in message
Thanks for that Jim, but what I need is more like this:
Dim appXL As Excel.Application
Dim WB As Excel.Workbook
For Each appXL In Windows 'I don't know if this is possible
For Each WB In appXL
If appXL.WB.Name = "myExcelFile.xls" Then
appXL.WB.SaveCopyAs "C:/myExcelFileDummy.xls" 'Save copy of file
Workbooks.Open Filename:="C:/myExcelFileDummy.xls" 'and open in active
End If 'Excel application
Next WB
Next appXL
I don't know if it is possible to cycle through all open Excel applications.



"Jim Cone" wrote:
The word to research is "automation"...

Sub OpenInstanceOfExcel()
Dim appXL As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set appXL = New Excel.Application
Set WB = appXL.Workbooks.Open("C:\Jims Documents\Software Users List.xls")
'Set WB = appXL.Workbooks.Add
Set WS = WB.Worksheets(1)
' Do your stuff with WS
Set WS = Nothing
WB.Close SaveChanges:=False 'Your choice
Set WB = Nothing
appXL.Quit
Set appXL = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"mcphc"
wrote in message
I have a VBA macro that uses two different excel files (workbooks) that are
open in one session of excel.

This works fine if each file is opened within one excel program. ie open
excel and then open both files via file-open within the one excel session.

If I open only one of the files in excel and then open another excel session
by clicking Start-Programs-Microsoft Office-Microsoft Offic Excel and then
use this excel session to open the other file the VBA will not run as it can
only see one of the files (workbooks).

Is there a way for excel to run a VBA between two different excel sessions?
Even if I could have the program use CopyAs to copy the workbook from one
excel session to the other.

Thanks