Macro in Excel. Run from Within Access

[deadlee]

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

[Damon Ostrander]

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.