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
Code:
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