Hi,
I am using this code to export data from Access to excel:
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.
I am using this code to export data from Access to excel:
Code:
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.