Formatting Excel via VB

Posted on 2000-03-17
Last Modified: 2010-05-02
I am currently working on an app in Access that uses Excel as the Report Generator, this way my client can edit the report with ease.  My question is how can I programmatically format the columns to fit the longest Item, and how can I format a cell with a custom format such as currency/Percentage, an how can I center text in a cell?

Thanks in advance.

Question by:jforbes
  • 2
  • 2
LVL 70

Expert Comment

by:Éric Moreau
ID: 2629040
Use this:


Author Comment

ID: 2629073
Sorry I guess I wasn't clear on the last part.  I know how to select a range, what method do I call or use once the range is selected to center columns and add custom formats.

Thanks so far the autofit works.


Accepted Solution

Chrynoble earned 100 total points
ID: 2629095
This is the VB code. It should translate well into access.

Sub UseExcelAsAReportingTool()
'Note this example doesn't include code to open a worksheet or close it.

'Set up a variable to identify the excel application
    Dim ExcelApp
'Set the variable to create a new Excel object
'You can also getobject to use an already open instance of excel.
    Set ExcelApp = CreateObject("excel.application")
'This is how you make whole columns fit the longest value
'Note: This also works with rows using rows("?#:?#").rows.entirerow
'This is how you enter a format for more information on specific formats consult Excel help
    ExcelApp.Range("A1:C5").NumberFormat = "0.00%"
'This is how you enter values in a cell
'The second method uses R1C1 style, the first does not.
    ExcelApp.Range("A1").Value = "This is how you enter a value in a cell"
    ExcelApp.Cells(1, 1).Value = "This does the same thing"
'Remember to clear the memory after you are done.
    Set ExcelApp = Nothing
End Sub

Expert Comment

ID: 2629109
The easiest way to find out what methods to use in Excel is to record a macro and perform the change. Looking at the automatically generated code will give you a guide to use.

Author Comment

ID: 2629137
Thanks a million, I never would have thought about using a macro, works like a charm.  Makes it nice when you are able to cheat honestly for code.

Thanks again.


Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

808 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