ChrisEberhart
New member
- Local time
- Yesterday, 21:29
- Joined
- Feb 6, 2017
- Messages
- 3
I am getting puzzling error when attempting to use VBA to create and save an Excel workbook. (I have searched the forum diligently.):banghead:
All works fine - I have been able to create and view the workbook - but the SaveAs generates the attached error. It appears to be generating a hex file name which changes each time it is run.
All works fine - I have been able to create and view the workbook - but the SaveAs generates the attached error. It appears to be generating a hex file name which changes each time it is run.
Code:
..... Set qd = db.CreateQueryDef("", strSQL)
Set rs = qd.OpenRecordset
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim file_str As String
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Range("a2").CopyFromRecordset rs
For i = 1 To rs.Fields.Count
xlApp.ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
xlApp.Cells.EntireColumn.AutoFit
xlBook.SaveAs ("C:\ComponentList.xlsx")
xlBook.Close
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing