Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New Worksheet (A2K)

    Good Morning,

    If I'm posting in the incorrect lounge, I apologize.

    I have data w/in ACCESS that I export to an EXISTING Workbook. This workbook should be updated on a bi-weekly basis. However, each time I run the report, I'm copying and pasting the data into a new worksheet within the existing workbook and then naming the tab for the current week the report was ran. Ideally, each week, I would like to run my report using automation (vba).
    1. <LI>Export ACCESS data to a NEW WORKSHEET within the existing WORKBOOK
      <LI>Name the new worksheet accordingly (by the week processed)
      <LI>Maintain the formating of a specific worksheet
    Is this possible? If so, is it complicated? If so, can someone point me to EXCEL samples I can follow.

    I'm somewhat knowledgeable with vba, although, primarily in ACCESS, but I believe I can follow samples, if needed.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: New Worksheet (A2K)

    There is an example of automating Excel from Access in <post:=654,900>post 654,900</post:>. If you search the Access forum for Excel.Application, you'll find more.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Worksheet (A2K)

    Thanks Hans for the response. I took your advice and found bits and peices and was able to put something together. The process works, however, I'm still having a few minor problems. I'm certain it's code placement, but can't figure it out.

    The code to export (paste) the data from Access to Excel resides within Access except for the portion for the margins. Access doesn't recognize the portion outlined in the attachment, so I've placed it within Excel as a macro and call the macro within Access.

    I've attached my code.
    The issues I'm having a problem with:
    <UL><LI>I ONLY want A1:K2 to be BOLD but if I bold one area, it all bolds, if I don't, none of it's bold
    <LI>Autofit Column
    <LI>Autofit Row[/list]Drawbacks:

    <UL><LI>The process can be viewed while it's running
    <LI>I must close Acces first to view the spreadsheet[/list]If you have any suggestions, I would certainly appreciate it.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: New Worksheet (A2K)

    When using Automation, you must take great care to refer ALL Excel objects directly or indirectly to the Excel application object. You should NEVER use something like

    Sheets("Main").Select

    because that way, Sheets does not belong to objXL or objWkb. Use

    objWkb.Sheets("Main").Select

    instead. On a further note, it's generally not necessary to select sheets and cells in a macro.

    See attached version.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: New Worksheet (A2K)

    Thanks Hans....wouldn't have been able to solve it on my own, EXCEL vba I know even less about than Access vba.

    I did however make 1 minor change.

    Changed:
    objSht.Range("A1").PasteSpecial Paste:=xlformats

    To
    objSht.Range("A1").PasteSpecial Paste:=xlAll

    and all works as expected.

    As always, thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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