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