MS Access vba how to select excel sheet following docmd.transferspresdsheet

Hi, Having just executed a do.cmd transferspreadsheet called say "My_New_Sheet" how can I select the sheet to start formating it.  I can open a new workbook and sheet with dim and set obj etc.
the line syntax I need is maybe something like
set objExcelSheet = objExcelWookbook.Worksheet ???? name??
Help!
downehouseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Hi downehouse, the attached code exports the results of a recordset and also formats the spreadsheet. You can modify this so it opens the output file from the transfer and then apply the format. The other option is to do an OutPutTo but this is limited to approximately 16000 rows.
Cheers, Andrew
Sub ExportToExcel()
Dim rst As DAO.Recordset
Dim strSheetName As String
Dim lngColumns As Long
Dim strLastColumn As String
Dim cntColumns As Long
Dim cntRows As Long
 
Dim objExcel As Object      ' New Excel.Application
Dim objWorkBook As Object   ' Excel.Workbook
Dim objSheet As Object      ' Excel Sheet
 
Const conXLAutoFormatStyle = 2
 
    Set rst = CurrentDb.OpenRecordset("qryExport_Orders")
    
    If rst.EOF Then
       MsgBox "No Data to Export"
    Else
 
       lngColumns = rst.Fields.Count
       If lngColumns <= 26 Then
          strLastColumn = Chr(64 + lngColumns)
       Else
          strLastColumn = Chr(64 + Int(lngColumns / 26)) & Chr(64 + lngColumns Mod 26)
       End If
       
       Set objExcel = CreateObject("Excel.Application")
       objExcel.SheetsInNewWorkbook = 1
       Set objWorkBook = objExcel.Workbooks.Add
       Do While Not rst.EOF
          If strSheetName <> rst(0) Then
             If strSheetName <> "" Then
                objSheet.Range("A1:" & strLastColumn & cntRows).autoformat Format:=conXLAutoFormatStyle _
                             , Number:=True, Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
                objWorkBook.sheets.Add
                objWorkBook.activesheet.Move After:=objWorkBook.sheets(strSheetName)
             End If
             
             Set objSheet = objWorkBook.activesheet
             
             strSheetName = rst(0)
             objSheet.Name = rst(0)
             
             cntRows = 1
             For cntColumns = 1 To rst.Fields.Count - 1
                 objSheet.Cells(cntRows, cntColumns) = rst.Fields(cntColumns).Name
             Next cntColumns
          End If
          
          cntRows = cntRows + 1
          For cntColumns = 1 To rst.Fields.Count - 1
              objSheet.Cells(cntRows, cntColumns) = Nz(rst.Fields(cntColumns), "")
          Next cntColumns
          
          rst.MoveNext
       Loop
       
       objSheet.Range("A1:" & strLastColumn & cntRows).autoformat Format:=conXLAutoFormatStyle _
                    , Number:=True, Font:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
       
       objWorkBook.sheets(1).Select
       Set objSheet = objWorkBook.activesheet
       objExcel.Visible = True
       
       
    End If
    
    rst.Close
    Set rst = Nothing
    
    MsgBox "Export Completed"
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
downehouseAuthor Commented:
Put me on the right track, solved now, many many thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.