Hi Dave,
I have an access application which opens an new excel application in the
background ( Set ExcelApp = New Excel.Application) and does it 'stuff' no
problem, to keep down the code lines I have use the same 'Public Sub'
proceedure with different values to get data from the excel spreadsheet in
the background.
The way to do this is to either (a) declare ExcelApp as a global variable, do
the 'Public sub' doesn't need to set it to anything:
Dim ExcelApp As Excel.Application
Sub Caller()
Set ExcelApp = New Excel.Application
DoSomething ExcelApp
End Sub
Public Sub DoSomething()
With ExcelApp.ActiveSheet
...
End With
End Sub
Or pass the instance into the 'public sub':
Sub Caller()
Dim ExcelApp As Excel.Application
Set ExcelApp = New Excel.Application
DoSomething ExcelApp
End Sub
Public Sub DoSomething(ByRef ExcelApp As Excel.Application)
With ExcelApp.ActiveSheet
...
End With
End Sub
Regards
Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk