Excel SaveAs Error 1004

[ChrisEberhart]

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.

 .....       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

 

[The_Doc_Man]

A quick search of the forum shows this thread with a similar error, and I see you are creating your own query. Read it to see whether it is applicable.

https://access-programmers.co.uk/forums/showthread.php?t=278280

If you are using SharePoint, this link may also be helpful.

https://social.msdn.microsoft.com/F...t-excel-cannot-access-the-file?forum=exceldev

Not sure whether it is relevant, but there is also some discussion with this error number regarding manipulations within the sheet and then closing, which I'm betting is where the problem becomes significant.

https://www.mrexcel.com/forum/excel...ening-excel-access-upon-second-execution.html

Finally, this thread refers to what happens if you run the process more than once in the same instance. It wasn't clear from your write-up if this ever works at all, or if it fails only after one successful run.

https://bytes.com/topic/access/answers/206763-run-time-error-1004-a

 

[ChrisEberhart]

Thanks, Doc_Man. I had seen a couple of those but I am finding them not helpful. If I comment the .SaveAs, I can run it over and over, being prompted each time on whether to save Book1 when .Close executes. I do save it and it opens fine.
I checked Task Manager, and there is only one instance of Excel running at the time of the error.

 

[ChrisEberhart]

It appears to be a path problem. I removed the path, .SaveAs("ComponentList.xlsx") I am working on a virtual destktop and the workbook is saved to my user directory. SO I can make it work for me, but not for others unless I can figure out how to catch the path to use later.

 

[The_Doc_Man]

Just as a point of consideration, CurrentDB.Name contains the current path in which the front-end file resides. If you parsed that to remove the xxx.MDB file name and type and put in the spreadsheet's name and type, you might get a usable dev:/path/ sequence.