Set Row Height in Exported (to Excel) table data
Hi
I have a button on a database that exports all main data to an Excel Spreadsheet but is there a way to automatically set the row height in Excel? At the moment users have to manually set the row height after export to make it easier to read.
This is the code i currently use
Code:
Public Function ExportToExcel() 'Variable declaration
Dim strQuery As String
Dim lCounter As Long
Dim rsRecordset As Recordset
Dim objExcel As Object
Dim wkbReport As Object
Dim wksReport As Object
'Create new excel file
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set wkbReport = objExcel.Workbooks.Add
Set wksReport = wkbReport.Worksheets(1)
'Set the query
strQuery = "SELECT * from tblIncidents"
'Execute the query on the database
On Error GoTo Error_Query
Set rsRecordset = CurrentDb.OpenRecordset(strQuery)
On Error GoTo 0
'Add header in row 1 of Excel sheet
For lCounter = 0 To rsRecordset.Fields.Count - 1
wksReport.cells(1, lCounter + 1).Value = rsRecordset.Fields(lCounter).Name
Next
'Export data to Excel sheet
wksReport.cells(2, 1).CopyFromRecordset rsRecordset
'Auto fit Excel columns to adjust as per data
wksReport.cells.EntireColumn.AutoFit
'Close the objects
Set rsRecordset = Nothing
Set wksReport = Nothing
Set wkbReport = Nothing
'Show the message to user
MsgBox "Done"
Exit Function
'Error handler if query does not execute
Error_Query:
MsgBox "Error: " & Err.Description, vbCritical
Exit Function
End Function
Thanks
Mykal