Copy Excel sheet into an existing Excel file

[StephenL]

Hello all,

Is it possible to copy an Excel worksheet into an existing Excel file using Access VBA coding?


Dim xlApp As Object
Dim ExlFile As Object
Dim ExlFile2 As Object
Dim FN1 as String
Dim FN2 as String

FN1 = "c:\YTD Reports summary.xls"
FN2 = "c:\ YTD Reports by BU.xls"

Set xlApp = CreateObject("Excel.Application")
Set ExlFile = xlApp.workbooks.Open(FN1).Sheets(1)
Set ExlFile2 = xlApp.workbooks.Open(FN2).Sheets(1)

ExlFile.Copy After:=ExlFile2 ? This is where I am stuck.


Any help would be greatly appreciated.

Steve

[geekgirlau]

If you declare ExlFile and ExlFile2 as "Worksheet" rather than "Object" this should work (you'll need a reference to the Excel object library in order to do this).

[StephenL]

Thanks - That worked.

[Norie]

Steve

The code you posted should work, how didn't it?

By the way declaring as Worksheet and referencing the Excel Object library is good programming practice.

But it's not really needed, and could in fact cause problems if you are distributing this code to or it will be used by people/machines with different versions of Excel.

[StephenL]

Norie - I was getting subscript errors. :banghead:

[Norie]

Steve

Can't quite see how that code would cause that.:dunno