Solved

Format row height and column width during Excel export

Posted on 2009-04-10
5
616 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now