Modify Excel Spreadsheet

[Kib]

Hello,
I am wondering if there is a way to modify an excel spreadsheet in an access module. I specifically am trying to change some properties in the spreadsheet's page setup. Such as landscape, and headers. Any help in this area would be useful. Thanks.

 

[MichaelRed]

I am a NOT fan of excel, so my advice is really just to get any 'information' out there in excel land and CAREFULLY bring it into Ms. Access tables and then uses it as you will. On the ohter hand, there have been MANY discussions of the OLS interface to Excel in these (MS. Access and VB) in recent weeks. I'm sure you could learn MORE than you want to know about manipulating excel from access just ny doing a keyword search on excel.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[gatecrasher]

are you trying to format it in excel or Access? If you are using excel you could use a module to call an excel macro from access.

 

[snoopy75]

First, make sure you have a reference to the Excel object library. Then open up the Object Browser and explore the various properties and methods available to you.

If you'd like more specific help ;-), here's some sample code to change the page orientation and header/footer of an excel worksheet:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

Set objXLBook = GetObject("C:\somefile.xls")
Set objXLApp = objXLBook.Parent
Set objXLSheet = objXLBook.Worksheets("Sheet1")

objXLSheet.PageSetup.Orientation = xlLandscape
objXLSheet.PageSetup.CenterHeader = "My Worksheet"
objXLSheet.PageSetup.CenterFooter = "Page &P of &N"

set objXLSheet = Nothing
set objXLBook = Nothing
set objXLApp = Nothing

Really, though, exploring the object browser and looking at the examples in the help is probably the best way to learn this stuff. Good luck!

--Ryan