Go Premium for a chance to win a PS4. Enter to Win


Formatting Excel via VB

Posted on 2000-03-17
Medium Priority
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 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

916 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