Trying to save and close excel (2007) from ms access (2007) vba

[iantowell]

Still learning Ms Access vba but learning fast.
I am not understanding fully and I am stumped on just on two things of which I am sure solve one, the other will also be solved. Spent the last 2 days with no progress.
I have a button on my form opening my excel spreadsheet (template), inserting my recordset from a table into Sheet1 and this is all working fine. My code is below

My problem is when I go to save and close I get the error:
Runtime Error 91:
Object variable or with block variable not set.

I thought I have set everything, but I do not understand what else I need 'set'? I am totally confused.

Can anybody show me what to do - Thank you in advance

My Code:
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet


Dim rs As Recordset


Set xlapp = New Excel.Application
MySheetPath = "z:\Template.xltx"


Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
Xl.Visible = True
XlBook.Windows(1).Visible = True


Set XlSheet = XlBook.Worksheets(1)


Set rs = CurrentDb.OpenRecordset("tbl_MyTable")


XlSheet.Range("A6").CopyFromRecordset rs


Set rs = Nothing
'Insert Row and the Value in the excel sheet starting at specified cell This is where I would put Date
XlSheet.Rows(2).EntireRow.Insert
XlSheet.Range("A3") = "ABC"


Xl.ActiveWorkbook.SaveAs FileName:="z:\Test.xlsx", FileFormat:=51 '<<<<<< Error 91 on this line and next line
Xl.ActiveWorkbook.Close


'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

 

[mole999]

when you step through the code, what is Xl.ActiveWorkbook declared as

 

[iantowell]

Hi,
Thank you for fast response.

I thought it would be under the Xl declaration, What do I need to declare "Xl.ActiveWorkbook" as? I am not full bottle on when, where etc to do declarations yet.

 

[mole999]

I was thinking that excel might have lost focus and wouldn't know what was the active work book hence having the error

 

[Micron]

Here's a guess. Mainly, you've started this code by interacting with Access, thus Excel is not the active window, therefore you cannot use ActiveWorkbook.

You've separately declared the application, workbook and worksheet, which is a good thing. So Access knows what your workbook is. Try saving by using only the object variable for workbook and closing by using only the application object variable (XlBook, Xl). You might want to consider a couple of things:
- declare these things as Object then it won't matter which version of Excel exists on the user's computer
- test that your recordset object contains records before trying to copy over
- using code tags and good indentation when posting more than a few lines of code. It's easier to decipher. I will admit to often just bowing out when too many code lines are posted as normal answer text.

 

[xenou]

This works for me:

Sub Foo()

    Dim XL As Object
    Dim wb As Object
    Dim ws As Object
    Dim myPath As String
    Dim rs As Recordset

    myPath = "C:\myTemp\TestTemplate.xlsx"

    Set XL = CreateObject("Excel.Application")
    Set wb = XL.Workbooks.Open(myPath)
    Set ws = wb.Worksheets(1)

    Set rs = CurrentDb.OpenRecordset("tbl_MyTable")
    ws.Range("A6").CopyFromRecordset rs
    ws.Rows(2).EntireRow.Insert
    ws.Range("A3") = "ABC"
    wb.SaveAs FileName:="C:\myTemp\Test.xlsx"
    wb.Close False

    Set XL = Nothing
    Set wb = Nothing
    Set ws = Nothing
    Set rs = Nothing

End Sub

I don't know much about templates so I would prefer to use a regular workbook as the "template". Also I do not trust GetObject() and never use it as it appears to have inconsistent behavior from my experience. In short, you can clean up this code and shorten it a lot which is always a good thing (I wouldn't even insert the extra row - just add the extra row already in your template!).

 

[Norie]

Why not use the reference to the workbook XLBook?

PS You shouldn't be using both CreateObject and GetObject, you might end up working with 2 separate instances of Excel.

 

[iantowell]

Thanks - Solved used the XlBook and all is working fine. Don't fully understand fully yet, but on my way.

 

[iantowell]

Thanks for this. I will try this in another project I am working on. Thanks to all who helped me.

 

[Micron]

Don't fully understand fully yet, but on my way.

Perhaps I was too verbose thus not clear in post 5. You were trying to save the application not what the application creates.