Excel Automation: the active area (Access & Excel 2003 SP2)

Topic

New Reply

Hey all! (and Hans

I am working with Access and Excel automation. My program transfers information from Access to Excel and then manipulates the formatting of the Excel spreadsheet to make it look uniform and pretty.

Is there an easy way to reference the part of the excel spreadsheet that contains data so that I can format just th epart of the spreadsheet that’s being used?

The reason for this is that I then want to set my print options to always format to 1 page width but multiple pages down, and if I accidentally format part of the spreadsheet I don’t want, then I get blank columns in the width on the right.

TIA!

Reply | Quote

Replies

Let’s say that you have a variable wsh of type Excel.Worksheet.
The used range of wsh (at least, what Excel considers to be the used range) is

wsh.UsedRange

If you want to refer to the table containing cell A1, you can use

wsh.Range(“A1”).CurrentRegion

Reply | Quote

Hans, that worked BEAUTIFULLY!

Can you possibly point me to a reference that would have this sort of information? I have a gazillion books here, but none of them have the phrase “CurrentRange” in them. I was searching for “ActiveRange” not knowing what I was looking for…it’s sooo confusing!

Thank you sooooo much!

Reply | Quote

PS. For example, another thing I’d like to do is set the margins of the excel spreadsheet so it’s ready to print…

What I’ve been doing is writing macros in excel and trying to get them to work in Access, but this particular one won’t work…and it would be nice to be able to see what other sorts of properties I can manipulate from Access to Excel.

Reply | Quote

You can do everything from Access that you also can do within Excel, but you must take care to refer explicitly to Excel objects created by you. For example, the following code is fine when run within Excel, but it is dangerous when run from Access:

With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(1)
End With

The reason is that you’re not in Excel, so ActiveSheet is not properly qualified. Instead of ActiveSheet, you should refer to a variable of type Excel.Worksheet:

With wsh.PageSetup

or to the active sheet within a workbook referred to by a variable of type Excel.Workbook:

With wbk.ActiveSheet.PageSetup

Reply | Quote

Actually, it was the whole “InchestoPoints” thing that threw me, it wasn’t necessary at all :-/

Thanks again! You are da man!!!

Reply | Quote

I generally like Excel Books by John Walkenbach – he has books about Excel in general, and about Excel VBA.

Reply | Quote