Use Excel VBA in Access

  • Thread starter Thread starter Mike Collard
  • Start date Start date
M

Mike Collard

I have asked a similar question before but not got a very
clear response.

I had hoped to be able to just cut and paste Excel macro
code into an Access module and then make a reference to
Excel and run the code but it doesn't seem that simple.

Any help appreciated.
 
yes, you can't do just copy paste. first you have to add a code to create
excel application
then paste excel code and modify it, that it "based" on opened sheet.

for example, if you have a code in excel:
Range("B2:B5").Select
Selection.Font.Bold = True
Selection.Font.Italic = True

in access should be:

Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add '(or open existing)
Set Sheet = xlApp.activeworkbook.sheets(1)
Sheet.Range("B2:B5").Font.Bold = True
Sheet.Range("B2:B5").Font.Italic = True

etc
HTH
 
Alex

Thank you - that is much clearer

Mike
-----Original Message-----
yes, you can't do just copy paste. first you have to add a code to create
excel application
then paste excel code and modify it, that it "based" on opened sheet.

for example, if you have a code in excel:
Range("B2:B5").Select
Selection.Font.Bold = True
Selection.Font.Italic = True

in access should be:

Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add '(or open existing)
Set Sheet = xlApp.activeworkbook.sheets(1)
Sheet.Range("B2:B5").Font.Bold = True
Sheet.Range("B2:B5").Font.Italic = True

etc
HTH
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com





.
Click to expand...
 
Back
Top