Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Macro in Excel. Run from Within Access

  1. #1
    New Member
    Join Date
    Mar 2002
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    I have a bit of VBA Code written specifically for excel macro.
    Now this macro needs to be run on a bunch of excel sheets, after which the sheet needs to be imported to access.

    I was wondering if there is a way that I can run the macro(code) from within Access, as it would help automate the entire process.

    I really don't want to set up a macro in the traditional way since the sheets change daily. and copying & pasting is out of the question.

    So basically teh point is :

    There is a new sheet everyday. Some lines need to be deleted etc. and cells need to be reformatted. Then the sheet is saved as text and imported into Access.

    How do I automate this process using VBA code. Can i run it from within access??

    Pls Help


  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Denver, Colorado USA
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    Hi deadlee,

    It is quite easy to adapt a macro that runs within Excel to run within Access instead, but still access the Excel objects. It is called Automation, and you can find out about it by looking up the topic "Understanding Automation" in the VBA helps. Basically all you have to do is create an object variable (using either GetObject or CreateObject) that points to the Excel application, and use that to qualify all the Excel objects. For example, where before you might have had the statements

    Range("B4") = "Hello"
    Range("B6:B12").Copy Destination:=Worksheets(2).Range("C9")

    they would now become

    With xlApp.ActiveSheet
    .Range("B4") = "Hello"
    .Range("B6:B12").Copy Destination:=xlApp.Worksheets(2).Range("C9")
    End With

    wher xlApp is the object variable you created with the CreateObject method. The use of a With statement can make things a lot easier when working across applications.

    I hope this helps.
    Keep Excelling.


    VBAexpert Excel Consulting
    LinkedIn Profile
    AllExperts Profile

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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