Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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
    Attached Images Attached Images
    • File Type: png x.png (13.7 KB, 0 views)

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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! <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Actually, it was the whole "InchestoPoints" thing that threw me, it wasn't necessary at all :-/

    Thanks again! You are da man!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •