[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Format row height and column width during Excel export

I am exporting to Excel via VBA a large, by row and column, data set. When I do, the columns and rows are both sized poorly. Would like to know how to set this at export. I'd prefer not to use an Excel template since I'd have to distribute it to a number of users.

Thanks in advance.
0
dglover
Asked:
dglover
  • 4
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
dglover,

The Export Command cannot format the Excel Column Widths, you will have to do this via code as well.

Put something like this on the end of your Export code:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Workbooks.Open "C:\YourFolder\YourFile.xls"
    objXL.activesheet.Cells.select
    objXL.activesheet.Cells.EntireColumn.AutoFit
    objXL.activesheet.Range("A1").select
    objXL.activeWorkbook.Save
    objXL.activeWorkbook.Close False
    objXL.Quit
End Sub

JeffCoachman


0
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry, that code will only autofit the Columns.

Here is the code that will autofit the Rows as well:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
    objXL.Visible = False
    objXL.Workbooks.Open "c:\tblDetailVideos.xls"
    objXL.activesheet.Cells.select
    objXL.activesheet.Cells.EntireColumn.AutoFit
    objXL.activesheet.Cells.EntireRow.AutoFit
    objXL.activesheet.Range("A1").select
    objXL.activeWorkbook.Save
    objXL.activeWorkbook.Close False
    objXL.Quit


Use like this:

Sub YourCode()
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

    'Your Code
    objXL.Visible = False
    objXL.Workbooks.Open "c:\tblDetailVideos.xls"
    objXL.activesheet.Cells.select
    objXL.activesheet.Cells.EntireColumn.AutoFit
    objXL.activesheet.Cells.EntireRow.AutoFit
    objXL.activesheet.Range("A1").select
    objXL.activeWorkbook.Save
    objXL.activeWorkbook.Close False
    objXL.Quit

'...
End Sub

JeffCoachman
   
     
0
 
Jeffrey CoachmanMIS LiasonCommented:
Obviously you will substitute your Excel file for mine
;-)

Jeff
0
 
dgloverAuthor Commented:
Jeff, Thanks. This took care of it.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-D
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now