Save to xl wksht, select method of range class failed

[Jewlrose]

I will post the code at the end. I found some coding to save data from a query to a particular worksheet in a particular excel file. It was working fine, but I was trying to find a way to make it close the excel file and application after. Every time I tried to edit it, I ended up with the following error:

1004
Select method fo Range class failed

Finally, I gave up and deleted the module and the macro that called it and remade it, creating a new module and pasting in the original coding. It still gives me the same error despite the fact that all instances of the "messed up" code are gone. Any ideas would be helpful.

Thanks in advance!!

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 ApXL As Object
    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)
 

    Set ApXL = CreateObject("Excel.Application")

 

    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
    xlWSh.Range("A1").Select
 

    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

 

[AccessMSSQL]

What line of code is this erring on?

 

[Jewlrose]

I'm sorry- I don't know. I am very new to VB. I have it being called from a macro and don't know how to get it to highlight which line of code it breaks down at. The Macro is just a RunCode macro where the function name (arguments) is the following:

SendTQ2XLWbSheet("Scores","Data","O:\TARGETS\2012\Fall\ScoreGraphs.xlsx")

It seems to open the excel file, but I don't think it is actually sending the query information to the file, or even selecting the data sheet in the file.

 

[boblarson]

Sorry, my bad (my code and it should get fixed, I just haven't yet).

It needs this:

    Set xlWSh = xlWBk.Worksheets(strSheetName)
 
[B][COLOR=red]    xlWSh.Activate[/COLOR][/B]
    xlWSh.Range("A1").Select

 

[Jewlrose]

That fixed it!! Many thanks.