Macro in Excel. Run from Within Access

deadlee

New Member
Joined
Mar 14, 2002
Messages
1
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top