Couple questions about programming excel objects in access

[Rob.Mills]

I've just created my first sub to open an excel template and take the data from a query and place it in the proper place on a worksheet.

Two things are happening that have got me confused.

One - I have to close the db in order to open the excel file. I've set all my object variables to nothing at the end of the sub so I don't get this one.

Two - When I open the new file all I get is a blank Excel window. No workbook shows up.

 

[jfgambit]

Rob:

I built something like this for Quickbooks transfers. Are you using something to the effect of

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strPath)

to open the Excel Application?

 

[Rob.Mills]

Slightly different

Dim XLApp as Excel.Application
Dim XLBook as Excel.Workbook
Dim XLSheet as Excel.WorkSheet

Set XLApp = CreateObject("Excel.Application")

Set XLBook=Set XLBook = Workbooks.Open("J:\Tax\Outsourcing\CP\Cash Database Report Internal Master.xlt")

Set XLSheet=XLBook.Worksheets("Sheet1")

 

[jfgambit]

Try:

Dim XLApp as Object
Dim XLSheet as Object

Set XLApp = CreateObject("Excel.Application")

Set XLSheet = XLApp.Workbooks.Open("J:\Tax\Outsourcing\CP\Cash Database Report Internal Master.xlt")

Also, is the MS office Excel 9.0 Object Library checked under references?

 

[Rob.Mills]

I did have the excel library checked and I tried what you suggested but didn't work.

 

[doulostheou]

I have never had the problems you experience with Excel, but I have a guess as to what might be going on. Review the basic code I use to interact with Excel. The comments I have included point to the items that I think might produce the problems you mentioned.

    Dim wsFax As Excel.Application

    Set wsFax = CreateObject("Excel.Application")
    wsFax.Workbooks.Open "FilePath"
    wsFax.Visible = True 'File will open but not be visible if this line is ommitted

    wsFax.ActiveWorkbook.SaveAs fileName:="NewFileName"
    wsFax.Workbooks.Close 'If you ommit this line and do not turn the file visible, the file will remain open and you will not be able to see it.

    Set wsFax = Nothing

My guess is that the file is invisible and not being closed, which could create some abnormalities when Excel tries to reopen it.