export to excel (1 Viewer)

[Bee]

Hi,

I am using this code to export data from Access to excel:

Public Function sendRep (qryName As String, pathname As String)
Dim objExcel As Object
Dim objWorkBook As Object
Dim i As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim ColLetter As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2003, qryName, pathname, True
  
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(pathname)

With objExcel
objExcel.DisplayAlerts = False
    
        With objExcel.Workbooks(.Workbooks.Count)
        
            For i = 1 To .Worksheets.Count
        
                    iRow = 1
                    While objWorkBook.Sheets(i).Cells(iRow, 1) <> ""
                        iRow = iRow + 1
                    Wend

                    ' This code grabs the last used row in column A
                    iCol = 1
                    While objWorkBook.Sheets(i).Cells(1, iCol) <> ""
                    iCol = iCol + 1
                    Wend
                        If iCol = 2 Then ColLetter = "A"
                        If iCol = 3 Then ColLetter = "B"
                        If iCol = 4 Then ColLetter = "C"
                        If iCol = 5 Then ColLetter = "D"
                        If iCol = 6 Then ColLetter = "E"
                        If iCol = 7 Then ColLetter = "F"
                        If iCol = 8 Then ColLetter = "G"
                                        
                    objWorkBook.Sheets(i).Select
                    objExcel.Range("A1:" & ColLetter & "1").Font.Bold = True
                    objWorkBook.Sheets(i).Cells.EntireColumn.AutoFit
                    objExcel.Range("A1:" & ColLetter & iRow - 1).Borders.Weight = 2
                        If i = 2 Then
                            objExcel.Range("A1:" & ColLetter & iRow - 1).AutoFilter
                        End If
                        If i = 3 Then
                            objExcel.Range("A1:" & ColLetter & iRow - 1).AutoFilter Field:=3, Criteria1:="ADRIAN KIRBY"
                        End If
            Next i
        End With
        .Visible = True
End With
End Function

The code exports data with all the headings horizontally layed out. In excel I use a OFFSET() to lay the headings out vertically.

I am just wondering if any one knows of any way to include the OFFSET() with VB code in the module. So, I don't have to write it each time I export to excel.

 

[shades]

Howdy. Not sure, but could you not use Transpose?
________
THE WAY FORWARD

 

[Bee]

Howdy. Not sure, but could you not use Transpose?

Hi,

I am not sure what transpose do. I will look at it later.

B