Trouble with Exporting to Excel

[Moxy1]

I'm having issues with selecting cells when I export queries with the below code. I keep getting an error "Select method of Range class failed" with the Sheet.Cells(2,1).Select portion. I'm able to get this to work when I only have 1 worksheet, but when I have multiple sheets I get the error.

Function ExcelExport()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim i As Integer
Dim intColumnCount As Integer
Dim j As Integer

Dim recExcelQuery As Recordset
Dim recSelectedField As Recordset

Dim strCurrentField As String
Dim strExcelCurrentValue As String
Dim strFileLocation As String
Dim strSelectedField As String

Dim recExcel As Recordset
Dim intExcelQuery As Integer
Dim strQueryName As String

On Error GoTo StopIt

 DoCmd.SetWarnings False

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add

Set recExcel = CurrentDb.OpenRecordset("tblExcel")  'Pulls list of queries to export
With recExcel
    Do Until .EOF
        strSelectedField = ![exc_query]
        intExcelQuery = intExcelQuery + 1
        strQueryName = ![exc_query]
    Set xlSheet = xlBook.Worksheets(intExcelQuery)
    Set recExcelQuery = CurrentDb.OpenRecordset(strSelectedField)
    Set Sheet = xlApp.ActiveWorkbook.Sheets(intExcelQuery)
    Set recSelectedField = CurrentDb.OpenRecordset(strSelectedField)

j = 1
intColumnCount = 0

With recSelectedField
If Not .EOF And Not .BOF Then
    recSelectedField.MoveFirst

    For i = 0 To recExcelQuery.Fields.Count - 1
        intColumnCount = intColumnCount + 1
        Sheet.Cells(j, intColumnCount).Value = recSelectedField.Fields(i).Name
        Sheet.Cells(j, intColumnCount).Font.Bold = True
        Sheet.Cells(j, intColumnCount).HorizontalAlignment = xlCenter
        Sheet.Cells(j, intColumnCount).Interior.ColorIndex = 48
        Sheet.Cells(j, intColumnCount).Borders.LineStyle = xlContinuous
        Sheet.Cells(j, intColumnCount).Borders.Weight = xlThin
    Next i  'Query field name
          
End If
End With

j = 2

recExcelQuery.MoveFirst
With recExcelQuery
    Do Until .EOF
intColumnCount = 0
        For i = 0 To recExcelQuery.Fields.Count - 1
        strExcelCurrentValue = recExcelQuery.Fields(i).Name
                intColumnCount = intColumnCount + 1
                strCurrentField = IIf(IsNull(recExcelQuery(i)), "", recExcelQuery(i))
                Sheet.Cells(j, intColumnCount).Value = strCurrentField
                Sheet.Cells(j, intColumnCount).HorizontalAlignment = xlRight
        
        Next i  'Query Field Name
StepOut:
   
    j = j + 1
    .MoveNext
    Loop
End With
    Sheet.Name = strQueryName
    Sheet.Cells.EntireColumn.AutoFit
    Sheet.Cells(2,1).Select
    xlApp.ActiveWindow.FreezePanes = True

recExcel.MoveNext
Loop
End With

    
    xlApp.DisplayAlerts = False

            strFileLocation = "C:\Spreadsheet"
            xlApp.ActiveWorkbook.SaveAs strFileLocation
    
    xlApp.DisplayAlerts = True
    xlApp.Quit
 
 DoCmd.SetWarnings True
Exit Function

StopIt:
    MsgBox Err.Description
    Resume
End Function

 

[SkipVought]

Hi,

Why do you need to SELECT anyhow? That sheet is probably NOT active and consequently, NOTHING in that sheet can be selected. But WHY? It is not performaing ANY useful function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[henniec]

Good day,
I am not sure if you are trying to copy all the records of a query to a spreadsheet in Excel. There is an easier method and more effective than the one you are using. The whole section you use to transfer data to Excel can be reduced to few lines by using .CopyFromRecordset.

You need to do the following:
Dim rs As Recordset
Set rs = db.OpenRecordset("Query name", dbOpenSnapshot)
Set objWkb = .Workbooks.Open(“conWKB_NAME”) ‘ the name of your workbook
Set objSht = objWkb.Worksheets("conSHT_NAME”) ‘ name of your worksheet
objWkb.Worksheets("conSHT_NAME ").Activate ‘ activate the worksheet, very important If you are going to use the same worksheet often create different worksheet names but take note that the name you use must be exactly the same for conSHT_name. No space in between, in front or back that do not appear in the worksheet tab. It will give you an error. With the above two steps you can move from one sheet to the next. Remember to always activate the sheet you are going to use.

objWkb.Windows("Quarterly").Visible = True ‘Make the worksheet visible to see whats happening
With objSht
.Cells(7, 10).HorizontalAlignment = xlCenter
.Range(.Cells(7, 10), .Cells(7, 11)).Merge
.Range(.Cells(8, 10), .Cells(8, 11)).Merge
.Range(.Cells(7, 1), .Cells(9, 14)).Font.Bold = True
.Cells(7, 1).Value = "ATG"
.Cells(7, 2).Value = "NAME"
.Cells(7, 3).Value = "MISA SAMPLES ONLY"

.Range("A11").CopyFromRecordset rs1
.Range("A12").CopyFromRecordset rs2

End With

In the above I have two records sets declared, part of about 7 queries I wanted to paste in consecutive rows. You can place the data starting at any cell within your worksheet. It seems to me as if you are using field names as your column headings. Not knowing exactly what you have in mind and if it will always be the same I add a few steps I use to populate the heading, format the heading and the column.

Hope this helps.

Hennie