Macro in Excel. Run from Within Access
Macro in Excel. Run from Within Access
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
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

    Thanks

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

    Default

      
    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.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

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
  •  

 

 
DMCA.com