1004 Select Method of Range Class Failed Error

[Ellie Sloss]

I was hoping someone may know how to resolve the 1004 Select Method of Range Class Failed error I'm getting. I’m using Bob's code for exporting data from Access to Excel via a macro. Here's the module code:

[SIZE=3][FONT=Calibri][SIZE=2]Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]' strTQName is the name of the table or query you want to send to Excel[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]' strSheetName is the name of the sheet you want to send it to[/FONT][/SIZE]
 
[FONT=Calibri][SIZE=2]' strFilePath is the name and path of the file you want to send this data into.[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=2]Dim rst As DAO.Recordset[/SIZE][/FONT]
[SIZE=2][FONT=Calibri]Dim ApXL As Object[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]Dim xlWBk As Object[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]Dim xlWSh As Object[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]Dim fld As DAO.Field[/FONT][/SIZE]
[FONT=Calibri][SIZE=2]Dim strPath As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=2]Const xlCenter As Long = -4108[/SIZE][/FONT]
[SIZE=2][FONT=Calibri]Const xlBottom As Long = -4107[/FONT][/SIZE]
[FONT=Calibri][SIZE=2]On Error GoTo err_handler[/SIZE][/FONT]
 
 
 
[FONT=Calibri][SIZE=2]strPath = strFilePath[/SIZE][/FONT]
 
 
 
[FONT=Calibri][SIZE=2]Set rst = CurrentDb.OpenRecordset(strTQName)[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=2]Set ApXL = CreateObject("Excel.Application")[/SIZE][/FONT]
 
 
 
[FONT=Calibri][SIZE=2]Set xlWBk = ApXL.Workbooks.Open(strPath)[/SIZE][/FONT]
 
 
[SIZE=2][FONT=Calibri]ApXL.Visible = True[/FONT][/SIZE]
 
 
[FONT=Calibri][SIZE=2]Set xlWSh = xlWBk.Worksheets(strSheetName)[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=2]xlWSh.Range("A1").Select[/SIZE][/FONT]
 
 
[FONT=Calibri][SIZE=2]For Each fld In rst.Fields[/SIZE][/FONT]
[SIZE=2][FONT=Calibri]ApXL.ActiveCell = fld.Name[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]ApXL.ActiveCell.Offset(0, 1).Select[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]Next[/FONT][/SIZE]
 
[FONT=Calibri][SIZE=2]rst.MoveFirst[/SIZE][/FONT]
 
[SIZE=2][FONT=Calibri]xlWSh.Range("A2").CopyFromRecordset rst[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]xlWSh.Range("1:1").Select[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]' This is included to show some of what you can do about formatting. You can comment out or delete[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]' any of this that you don't want to use in your own export.[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]With ApXL.Selection.Font[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Name = "Arial"[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Size = 12[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Strikethrough = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Superscript = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Subscript = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].OutlineFont = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Shadow = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]End With[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]ApXL.Selection.Font.Bold = True[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]With ApXL.Selection[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].HorizontalAlignment = xlCenter[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].VerticalAlignment = xlBottom[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].WrapText = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].Orientation = 0[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].AddIndent = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].IndentLevel = 0[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].ShrinkToFit = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri].MergeCells = False[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]End With[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]' selects all of the cells[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]ApXL.ActiveSheet.Cells.Select[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]' does the "autofit" for all columns[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]ApXL.ActiveSheet.Cells.EntireColumn.AutoFit[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]' selects the first cell to unselect all cells[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]xlWSh.Range("A1").Select[/FONT][/SIZE]
 
 
[FONT=Calibri][SIZE=2]rst.Close[/SIZE][/FONT]
 
 
[SIZE=2][FONT=Calibri]Set rst = Nothing[/FONT][/SIZE]
 
 
[FONT=Calibri][SIZE=2]Exit_SendTQ2XLWbSheet: [/SIZE][/FONT]
[SIZE=2][FONT=Calibri]Exit Function[/FONT][/SIZE]
 
 
[SIZE=2][FONT=Calibri]err_handler:[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]DoCmd.SetWarnings True[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]MsgBox Err.Description, vbExclamation, Err.Number[/FONT][/SIZE]
[SIZE=2][FONT=Calibri]Resume Exit_SendTQ2XLWbSheet [/FONT][/SIZE]
[FONT=Calibri][SIZE=2]End Function[/SIZE][/FONT]

I just need resulting data to spit out into a separate tab into 3 templates, so I have 15 runcode actions at the end for each query (ie, SendTQ2XLWbSheet("3a- b KPI query", "KPI Data p2", "U:\Data\KPI Template.xlsx"),
SendTQ2XLWbSheet("3b- b KPI query", "KPI Data p1", "U:\Data\KPI Template.xlsx"),
).
I’m getting the 1004 Select Method of Range Class Failed Error once I get to the RunCode actions to export, and only some of the data is successfully exporting.[/SIZE]

Is there something I'm missing? Any help would be greatly appreciated!
Thanks,
Ellie

 

[boblarson]

Okay, the first problem is that you aren't saving the Excel file in the code. The code which you have used only opens the file and puts it in but doesn't save and close the workbook.

So you would want to add the code to close. And since you will be doing this for many queries, I think it would be better to modify this code so that we move the Excel application object out to a Private Variable in the module this code is in and then not instantiate it each time and don't set it to nothing each time, but only when done. So, the module would look like this:

Option Compare Database
Option Explicit
 
Private ApXL   As Object
 
Private Function OpenExcel()
    If ApXL Is Nothing Then
        Set ApXL = CreateObject("Excel.Application")
    End If
End Function
 
Private Function CloseExcel()
    If ApXL Is Not Nothing Then
        ApXL.Quit
        Set ApXL = Nothing
    End If
End Function
 
Public Function SendTQ2XLWbSheet(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.

    Dim rst    As DAO.Recordset
    Dim xlWBk  As Object
    Dim xlWSh  As Object
    Dim fld    As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
 
    strPath = strFilePath
 
    Set rst = CurrentDb.OpenRecordset(strTQName)
 
[B]    ' calls the OpenExcel Function
    [COLOR=red]OpenExcel[/COLOR][/B]
 
    Set xlWBk = ApXL.Workbooks.Open(strPath)

    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Range("A1").Select

    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst

    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting. You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With

    ApXL.Selection.Font.Bold = True

    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With

    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells
    ApXL.ActiveSheet.Range("A1").Select
    xlWBk.Close True

    rst.Close

    Set rst = Nothing

Exit_SendTQ2XLWbSheet:
    Exit Function

err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheet
End Function

And after all of the calls to the function, you would call the

CloseExcel

function.

 

[Ellie Sloss]

Oops, that's why it was misbehaving.
I'll give it a try, thank you for your time, Bob!

Ellie