Table Export Issue

[juneau730]

Hey all, having a bit of a hiccup up with my table export function from my dBase. It's set to export to .xls, but I would really like it to be .xlsx since we are running Office 365/2016. However, when I change the output parameters to .xlsx, I get an error in my formatExcellSS code.

Any thoughts on why this is happening would be great.

Here is my cmdExport code

Private Sub cmdExport_Click()    Dim arrCurrentFormInfo
    Dim strCurrentForm
    Dim strCaption
    Dim strOutputFileName
    Dim strRecordSource
    Dim strOutputFile
    Dim objShell
    
    Set objShell = VBA.CreateObject("wscript.shell")
    
    arrCurrentFormInfo = Split(txtCurrentForm.Value, ":")
    If UBound(arrCurrentFormInfo) = 1 Then
        strCurrentForm = Replace(arrCurrentFormInfo(0), "form.", "")
        strOutputFileName = arrCurrentFormInfo(1)
    Else
        strCurrentForm = Replace(txtCurrentForm.Value, "form.", "")
        strOutputFileName = strCurrentForm
    End If
    
    strRecordSource = Me!NavigationSubform.Form.RecordSource


    strOutputFile = Application.CurrentProject.Path & "\" & strOutputFileName & ".xlsx"
    'DoCmd.OutputTo acOutputForm, frmCurrentForm, acFormatXLSX, Application.CurrentProject.Path & "\" & txtOutputFileName & ".xlsx", -1
    If Left(strRecordSource, 3) = "tbl" Then
        DoCmd.OutputTo acOutputTable, strRecordSource, acFormatXLS, strOutputFile
    Else
        DoCmd.OutputTo acOutputQuery, strRecordSource, acFormatXLS, strOutputFile
    End If
    
    Call FormatExcelSS(strOutputFile, "B1")
    objShell.Run "Excel.exe" & " " & Chr(34) & strOutputFile & Chr(34), , True
End Sub

Here is the FormatExcelSS coding, and it errors our on the Bold/Red line.

Sub FormatExcelSS(sXLFile, strSortRange)    Dim xlApp, xlWB
    Dim objRange, objRange2
    
    Set xlApp = VBA.CreateObject("excel.application")
[COLOR=#ff0000][B]    Set xlWB = xlApp.Workbooks.Open(sXLFile)[/B][/COLOR]
    With xlWB 'open file        With .ActiveSheet
            .Rows(1).Font.Bold = True '1.  Bold the headers (always in row 1)
            .Rows(1).Interior.ColorIndex = 15
            .AutoFilterMode = False 'turn off any existing autofilter just in Case
            Set objRange = .UsedRange
            objRange.Columns.ColumnWidth = 100
            .Rows.AutoFit
            .Rows(1).AutoFilter '2. Turn on AutoFilter for all coloms
            .Columns.AutoFit '3. Set Column width to AutoFit Selection
            '4. Set a freeze under column 1 so that the header is always present at the top
            .Range("A2").Select
            xlApp.ActiveWindow.FreezePanes = True
            'Sort on specified column
            Const xlAscending = 1
            Const xlYes = 1
            Set objRange2 = .Range(strSortRange)
            objRange.Sort objRange2, xlAscending, , , , , , xlYes
        End With
        .Worksheets(1).Activate
        .Close True 'save and close
    End With
    xlApp.Quit
    Set xlApp = Nothing
End Sub

 

[Joe4]

In your "DoCmd.OutputTo" command, you need to choose acFormatXLSX instead of acFormatXLS.
See: https://access-excel.tips/access-vba-docmd-outputto-method/

 

[juneau730]

Thanks Joe4, that partially fixed it. Now the data that was being exported as .xls is output as .xlsx
However, now other data isn't exporting correctly. Let me dig into it some and if I can't see why, I'll throw something up in a bit.

 

[Joe4]

Note that you had that line in multiple places.

 

[juneau730]

I think I got it all fixed now.
It was exporting the tables, but it wasn't opening them automatically like it should. When I tried to open it, there were errors, come to find out the date/time fields in the tables weren't set properly, so it didn't like the output.

Again, thank you for your help! Awesome resources on this site.

 

[Joe4]

You are welcome.
Glad you got it working now!