Trouble auto formatting exported excel spreadsheet (1 Viewer)

[Hecronis]

I have a query that I want to be able to export to an excel spreadsheet with the date appended to the file name and then also auto-format the spreadsheet. I can get the export and the date to append just fine using this module

Public Sub AutoExportReceipts()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QryReceipt_WorkOrders", "C:\Access\Receipts-WorkOrders_" & Format(Date, "yyyy-mm-dd") & ".xlsx", True
End Sub

I can't get the auto-format part to work though. I searched the web and tried a couple of different things but couldn't get them to work

This gave me a run-time error 1004 saying that it can not find the file even though the error spells the file name and path correctly:

Public Sub AutoFormatReceipts()
    wkbookPath = "C:\Access\Receipts-WorkOrders_" & Format(Date, "yyyy-mm-dd") & ".xlsx"""
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    With XL
        .Visible = False
        .displayalerts = False
        .Workbooks.Open wkbookPath
        .columns("A:XFD").EntireColumn.AutoFit
        .ActiveWorkbook.Close (True)
        .Quit
    End With
    Set XL = Nothing
End Sub

and this one gives me a run-time error 432 saying the file name or class name not found during the automation process:

Public Sub AutoFormatReceipts()
On Error Resume Next
Set XL = GetObject(, "Excel.Application")
On Error GoTo 0
If XL Is Nothing Then
  Set XL = CreateObject("Excel.Application")
End If

Set XlBook = GetObject("C:\Access\Receipts-WorkOrders_" & Format(Date, "yyyy-mm-dd") & ".xlsx""")

XL.Visible = True
XlBook.Windows(1).Visible = True
'xl.ActiveWindow.Zoom = 75

Set xlsheet1 = XlBook.Worksheets(1)

With xlsheet1
    .columns("A:XFD").EntireColumn.AutoFit
    .rows("1:1").Font.Bold = True
    .ActiveWorkbook.Close (True)
    .Quit

End With
Set XL = Nothing
End Sub

I don't know if I adapted these correctly or not or if this is not the way I should be doing it at all. Any suggestions would be great.

 

[JHB]

I think the problem is you've to many quotes:

wkbookPath = "C:\Access\Receipts-WorkOrders_" & Format(Date, "yyyy-mm-dd") & ".xlsx"""

 

[Hecronis]

Thank you. That worked! I wondered if it would be something simple like that lol

 

[JHB]

You're welcome, good luck.