[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1291
  • Last Modified:

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!
0
downehouse
Asked:
downehouse
1 Solution
 
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
 
downehouseAuthor Commented:
Put me on the right track, solved now, many many thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now