Solved

Format row height and column width during Excel export

Posted on 2009-04-10
5
635 Views
Last Modified: 2013-11-28
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
Comment
Question by:dglover
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24120783
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 24120822
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24120828
Obviously you will substitute your Excel file for mine
;-)

Jeff
0
 

Author Closing Comment

by:dglover
ID: 31569106
Jeff, Thanks. This took care of it.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24123246
;-D
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question