Consulting

Results 1 to 3 of 3

Thread: Set Row Height in Exported (to Excel) table data

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location

    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

    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
    Last edited by SamT; 04-16-2021 at 03:06 PM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
        'Auto fit Excel columns to adjust as per data
           wksReport.cells.EntireColumn.AutoFit
    Try
        'Auto fit Excel Cells to adjust as per data
           wksReport.cells.AutoFit
    Or
     wksReport.UsedRange.Cells.Autofit
    If that doesn't work, expand them both
           wksReport.Columns.AutoFit
           wksReport.Rows.AutoFit
    Or
    With wksReport.UsedRange
       .Columns.AutoFit
       .Rows.AutoFit
    End With
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •