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
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