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
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
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

719 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