Handling Excel files

J

judith

I am trying to download some data from access into Excel and then copy it
into an existing excel file

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

' Make it visible
xlApp.Visible = True

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

' save down data
DoCmd.OpenQuery "rptQry003ReconBaseTable"

' download Access Query to excel

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

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

' copy in data

xlWorkBook1.Activate
.....

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

any suggestions please
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

saving to excel 0
Copying across workbooks 1
Working outlook via vba code with xlsx - error 0
clearing to nothing? 0
Relpicating an Excel Macro in Access 4
export file to excel in microsoft access 1
insert an excel sheet 7
Excel Add-Ins 5

Top