runtime error 438 .saveas method

[etk]

Trying to create loop to save a bunch of workbooks. I keep getting runtime error 438 on the .saveas method. Any thoughts as to a resolution for this

Option Compare Database

Private Sub query_loop()



Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer


Set mydb = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * From tbl_Meetings;", dbOpenDynaset)


With rs
    .MoveFirst
    Do Until rs.EOF
    
    	Dim rs1 As DAO.Recordset
    	Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM EmpData WHERE EmpID = " & rs!Employee_ID & ";", dbOpenDynaset)
    	
	'create file string for saving workbook
	Dim file_str As String
    	file_str = "C:\Desktop\Meeting_Info_Sheets\Employee_Info_Meeting_ID_" & rs!Meeting_ID & ".xlsx"

    
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
 

        With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("Sheet1").Select
        .ActiveSheet.Range("A2").CopyFromRecordset rs1
        
	'Add column heading names to the spreadsheet
        For i = 1 To rs1.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = rs1.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
        xlApp.SaveAs FileName:=file_str, FileFormat:=xlWorkbookXML
        
        xlApp.Close
        End With
        
    Set xlApp = Nothing
    Set rs1 = Nothing

    rs.MoveNext
    Loop
End With

Set rs = Nothing

End Sub

 

[CJ_London]

what is the description for error 438? - the answer can usually be found there - or at least a clue as to the problem

 

[etk]

sure, 438 is object does not support this method...which is true of excel application object...it does not support the .saveas method

what I am asking is there an alternative for saving this? it seems I should declare a workbook and .saveas from that object, but I came away with similar results when I tested that. how do I incorporate a workbook object into what I am trying to do here?

 

[CJ_London]

OK,

I'm not that familiar with the excel object, but if you temporarily add the excel library and declare your objects as excel objects the typeahead will tell you what your options are - my guess is it would be .save rather than .saveas.

The other thing is that the object you are opening is an app - I would have though you would want to be saving a workbook so I would also guess you are missing a workbook object or you should be using something like

xlApp.workbook.SaveAs

 

[etk]

OK,

I'm not that familiar with the excel object, but if you temporarily add the excel library and declare your objects as excel objects the typeahead will tell you what your options are - my guess is it would be .save rather than .saveas.

The other thing is that the object you are opening is an app - I would have though you would want to be saving a workbook so I would also guess you are missing a workbook object or you should be using something like

xlApp.workbook.SaveAs

That gives some food for thought. I had once written a VBA program that went like this:

Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)

With outappt
'
'Enumerate AppointmentItem properties here
'
End With

I was thinking now that maybe I do something similar like:

    Dim xlApp As Excel.Application
    Dim xlwb As Excel.Workbook
    Set xlApp = CreateObject("Excel.Application")
    Set xlwb = xlApp. '<---- not really sure what property to use here

With xlwb
'
'enumerate workbook
'
xlwb.saveas filename:= file_str
End with

Does this make sense? Am I on the right path?

 

[CJ_London]

Sorry, I'm only guessing where to look, don't have the time to look myself.

from your example I suggest you try

Set xlwb = xlApp.createitem(xlWorkbook)

and then I think you will need to create a worksheet in the workbook object.

If you have added the excel library you can use the object browser (in the vba window, hit F2) to see all the members within the excel library. click on globals (or search for xlworkbook) you will find it and right at the bottom you will find its value (1)

 

[kyo]

hi,
i have changed your original code a little bit.

Sub query_loop()

Dim mydb As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

Set mydb = CurrentDb
Set rs = CurrentDb.OpenRecordset("Select * From tbl_Meetings;", dbOpenDynaset)

With rs
.MoveFirst
Do Until rs.EOF

Dim rs1 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM EmpData WHERE EmpID = " & rs!Employee_ID & ";", dbOpenDynaset)

'create file string for saving workbook
Dim file_str As String
file_str = "C:\Desktop\Meeting_Info_Sheets\Employee_Info_Meeting_ID_" & rs!Meeting_ID & ".xlsx"

' Dim xlApp As Object
' Set xlApp = CreateObject("Excel.Application")
Dim xlApp As Excel.Application
Dim xlBook As Workbook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

xlSheet.Range("a2").CopyFromRecordset rs1


' With xlApp
' .Visible = True
' .Workbooks.Add
' .Sheets("Sheet1").Select
' .ActiveSheet.Range("A2").CopyFromRecordset rs1

'Add column heading names to the spreadsheet
For i = 1 To rs1.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = rs1.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
'xlApp.SaveAs FileName:=file_str, FileFormat:=xlWorkbookXML
xlBook.SaveAs (file_str)

' xlApp.Close
xlBook.Close
xlApp.Quit

' End With

Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

Set rs1 = Nothing
rs.MoveNext
Loop
End With
Set rs = Nothing
End Sub

p.s. make sure you add a reference, Microsoft Excel 14.0 Object Library in case of Access 2010.

 

[etk]

Thanks Kyo. I was just going to ask if it was the workbooks property that I should be using.