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.

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

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

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

B