Copying across workbooks

[judith]

I am working from Access and trying to copy and paste data between two
workbooks.



' Create a new instance of Excel
Set xlApp = New Excel.Application

' Make it visible
xlApp.Visible = True

' Open an associated file
Set xlWorkBook1 = xlApp.Workbooks.Open(strInputFileName)

' download Access Query to excel in same folder as associated file

strInputFilePath = xlWorkBook1.Path
DoCmd.TransferSpreadsheet acExport, 8, "tbl99ReconRepBase",
strInputFilePath & "\sheet15temp.xls"

' Open the downloaded file
Set xlWorkBook2 = xlApp.Workbooks.Open(strInputFilePath &
"\sheet15temp.xls")

' copy some data over

xlWorkBook1.Activate
etc ....

This seems to work fine except if Excel is already open. In which case it
gives an error on the last line
Run time error 1004
Method 'Sheets' of object Global failure

I have declared xlWorkbook1,2 as workbooks. Any suggestions please

 

[papou]

Hello Judith
You may consider using the GETOBJECT and CREATEOBJECT functions:

Dim XlApp As Object
On Error Resume Next
Set XlApp = GetObject(,"Excel.Application")
If Err <> 0 Then
Set XlApp = CreateObject("Excel.Application")
On Error Goto 0
End If

XlApp.Visible = True
'etc....

HTH
Cordially
Pascal