Saveas method with workbook (1 Viewer)

[Kingz]

Hi guys, I'm trying to simply export data in a recordset to an excel file, which I show in the code. Unfortunately, only a manual entry of the expiry file and path is possible. Here is my code:

Set rs= currentDB. Openrecordset("dynamic_query", opensnapshot)

Set objxl = CreateObject("Excel.Application")
Set objwkb = objxl.workbook.add

With objwkb.Worksheets(1)
.cells(1,1).copyfromrecordset rs
End with

objwkb.saveas filename:="h:\aaa\bb\output.xls"

Objxl.quit
Set objxl = nothing

I get an error 1004, meaning the saveas method could not be executed.

Instead of the .saveas file, the line below works, but that forces me to manually type the file and path:

Objwkb.close savechanges:=True

 

[Gasman]

This worked for me
No idea as to how you got to the save as line as your code fell over at

OpenSnapshot
Set objwkb = objXL.Workbook.Add
I would expect you need to say what type of file as well, as when I opened the excel file, Excel told me it did not match the extension?

Note your typos.

Sub TestXLRS()
    Dim objXL As Excel.Application
    Dim objwkb As Excel.Workbook
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset(" Select * from twgonder", dbOpenSnapshot)

    Set objXL = CreateObject("Excel.Application")
    Set objwkb = objXL.Workbooks.Add

    With objwkb.Worksheets(1)
        .Cells(1, 1).CopyFromRecordset rs
    End With

    objwkb.SaveAs FileName:="F:\Temp\twgonder.xls"

    objXL.Quit
    Set objXL = Nothing
End Sub

 

[DickyP]

Just tried your code and after correcting what appeared to be a couple of typos (eg objxl.workbooks.add and dbOpenSnapshot) and adding variable declarations it worked perfectly. Makes me think you might be trying to write to a mapped network drive where such behaviour can happen.

In addition your target filename has the extension .xls - eg, Excel 97-2000 file type which could (possibly) cause a problem in a highly controlled environment.

However, the code begs the question anyway of why you didn't just run DoCmd.Transfer spreadsheet, which on my PC works at least twice as fast for the record set of 120 plus records I used!

 

[Kingz]

This worked for me
No idea as to how you got to the save as line as your code fell over at

OpenSnapshot
Set objwkb = objXL.Workbook.Add
I would expect you need to say what type of file as well, as when I opened the excel file, Excel told me it did not match the extension?

Note your typos.

Sub TestXLRS()
    Dim objXL As Excel.Application
    Dim objwkb As Excel.Workbook
    Dim rs As DAO.Recordset
   
    Set rs = CurrentDb.OpenRecordset(" Select * from twgonder", dbOpenSnapshot)

    Set objXL = CreateObject("Excel.Application")
    Set objwkb = objXL.Workbooks.Add

    With objwkb.Worksheets(1)
        .Cells(1, 1).CopyFromRecordset rs
    End With

    objwkb.SaveAs FileName:="F:\Temp\twgonder.xls"

    objXL.Quit
    Set objXL = Nothing
End Sub

First of all, thanks.. I've got a problem with this, which might explain the general difficulties I'm having. I can't declare like you have. I get a compilation era. To elaborate, I can't say dim objXL as excel application. It gives me a compiling error. Why? In my reference, I have Microsoft office 16.0 access database engine object. Oh, and it's Access 2021 I'm using, if that helps.

 

[Gasman]

You would need to use the reference for Excel (and word ) if you want to use them as early binding.
For late binding declare them as Objects.

This is mine, so I can use early binding and intellisense