Can I format an Excel spreadsheet from Access? 1

[BobK4UVT]

My client likes to have their "reports" exported as Excel spreadsheets. I do the analysis in Access and then export the table to Excel.

The resulting spreadsheet needs to have some formatting done on it before the client receives it. Fairly simple stuff --- Things like centering and/or bolding a column, setting column with to "best fit", ensure it's proprly sorted, etc. I also need to apply conditional formatting on two columns --- bold the cell and apply highlight colors to the cells bsed on cell contents.

Is it possible to do this kind of formatting from VBA in Access? It sure would be nice to automate it and have it formatted in final form, rather than opening the spreadsheet in Excel and doing the formatting manually. Sure would be a time-saver!!

Many TIA!

Bob

 

[Reinier10]

Hi Bob,

Yes this can be done through automaton using OLE.
See the following article on how to create an excel object:

http://word.mvps.org/FAQs/InterDev/EarlyvsLateBinding.htm

Then look into the excel object library on how to manipulate the formatting of cell-ranges. I'll type some pseudo code give to you an idea from what I mean to say:
(using early binding you need to add the Excel object into your references0

Dim oXL As Excel.Application
dim oWb as Excel.Workbook
dim oSh as Excel.Worksheet


Set oXL = New Excel.Application
Set oWb = Excel.Workbooks.open(YOURWORKBOOK)
Set oSh=oWb.SHeets(YOURSHEET)

With oSh 'do your formatting here
with .cells(1,1)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
' etcetera
end with 'cells

end with 'oSh


I suggest recording a macro from excell where you fromat things the way you want them to appear and then copy that into the formatting part of your code (after some modifications of course)

Hope that helps,

Reinier

 

[BobK4UVT]

Wow!! That was a quick reply!! Thanks for the tip! I'll play with it and then let you know how it went. I had planned to record a macro in Excel & use that as a baseline.

Bob

 

[BobK4UVT]

Reinier,

Works like a charm!! Recorded the macro first & tweake it a little. I notice the generated code from the macro does an awful lot of ActiveWindow.ScrollRow = xxx. I'm curious why, so I'll have to dig into the Excel API a bit more to understand what it is doing.

At the end of my sub, it does come up with a dialog box saying "Do you want to save........". I expect I just need to be sure that it closes the Excel down properly & then I'll be home free.

Thanks also for the link on early binding --- always good to learn new tricks!!

Thanks a million for the tip --- it'll save me a lot of manual formatting. And I can use the same methods for all other reports, too!! You've been a GREAT help with this tip and I'm most appreciative!!

Take care --------- Bob

 

[Reinier10]

Hi Bob,

Nice that I could help. About closing the workbook:
you can actually close it silent by :

oWb.close, false (try the help on the close method for more info)

Do not forget to clean up properly:

set oSh=nothing
Set oWB = nothing
Set oXl = nothing
In the order from "child" to "parent"

Bye
Reinier