Run excel macro from access. 2

[danvzla]

Hi experts.

Is there a way of running an excel VBA macro from a form of access or have a procedure on access that opens an excel file and then runs the macro.

Thanks!

Daniel

 

[xlbo]

Yup

Option Compare Database
Option Explicit
Sub OpenExcel()

Dim objExcelApp As New Excel.Application
Dim objExcel As Object
'create Excel
Set objExcelApp = New Excel.Application
'open file
Set objExcel = objExcelApp.Workbooks.Open("D:\booknew.xls&quot
'make it visible if you want
objExcelApp.Visible = True
call objExcel.SubName
Set objExcel = Nothing 'clears memory
set objExcelApp = nothing
end sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[danvzla]

Thanks Geoff.

When I try to run this code I'm getting this error-msg : "user-defined type not defined" on "Dim objExcelApp As New Excel.Application"

Regards,

Daniel

 

[PHV]

Have you referenced Excel in your VBA project ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VulcanJedi]

it crashed when running macro?! and yest referenced XL

Tranpkp

 

[xlbo]

Depends what the macro does - if it selects sheets and the like, excel will need to have the focus

What's the error and what line did it "crash" on ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[VulcanJedi]

Outlook vba, event is recognized, code runs, opens XL, specified workbook, then 'crashes' when trying to find the macro as specified?

Tranpkp

 

[VulcanJedi]

call objExcel.SubName

Tranpkp

 

[PHV]

tranpkp, you have a public Sub named "SubName" in the Module sheet of your workbook ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VulcanJedi]

yes sir i do in a module

Tranpkp

 

[xlbo]

so you have
sub SubName()
'code
end sub

in a module ??


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[xlbo]

I do apologise - having tried this again, it would appear not to work on XP

try this instead:

Option Compare Database
Option Explicit
Sub OpenExcel()

Dim objExcelApp As New Excel.Application
Dim objExcel As Object
'create Excel
Set objExcelApp = New Excel.Application
'open file
Set objExcel = objExcelApp.Workbooks.Open("D:\booknew.xls&quot
'make it visible if you want
objExcelApp.Visible = True
objExcelApp.Run "SubName"
Set objExcel = Nothing 'clears memory
set objExcelApp = nothing
end sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244