VBA, Run Excel Macro through Access VBA

Run Excel Macro through Access VBA

Author	Message
Scott Holtzma #1 / 6	Run Excel Macro through Access VBA Is there a way, via a command or another way, to open and run an Excel macro stored in a workbook through Access???
Mon, 05 Dec 2005 05:28:17 GMT

Ken Snel #2 / 6	Run Excel Macro through Access VBA Here's some sample code for opening an EXCEL workbook and running a VBA macro that's in the workbook in a public module: Public Sub TestMacroRun() Dim xlx As Object, xlw As Object Set xlx = CreateObject("excel.application") Set xlw = xlx.workbooks.Open("C:\Filename.xls") xlx.Run "Filename.xls!MacroName()" xlw.Close False xlx.Quit Set xlw = Nothing Set xlx = Nothing End Sub -- Ken Snell <MS ACCESS MVP> Quote: > Is there a way, via a command or another way, to open and run an Excel macro > stored in a workbook through Access???
Mon, 05 Dec 2005 06:18:23 GMT

Tim Ferguso #3 / 6	Run Excel Macro through Access VBA Quote: > Is there a way, via a command or another way, to open and run an Excel > macro stored in a workbook through Access??? I found this through trial and error, but from memory it was something like Set xlbMyWorkBook = GetObject("d:\workbooks\mydata.xls") Call xlbMyWorkBook!MyModule!MyProcName but I could be wrong.... Best wishes Tim F
Mon, 05 Dec 2005 06:36:02 GMT

Scott Holtzma #4 / 6	Run Excel Macro through Access VBA Ken.. Thanks for this mock code. I have applied to my existing needs and can not get past the xlx.Run statement. When the code gets to that point it says: Run-time error '1004': The macro 'CDSDataCleanMacros.xls!CleanTradeandRefEntityData()' cannot be found. Do you possibly now what is wrong with this piece of the code given my code below, based on your suggestion???? Public Sub CleanDownloadedTradeDatainExcel() Dim xlx As Object, xlw As Object Set xlx = CreateObject("excel.application") Set xlw = xlx.Workbooks.Open("F:\GFIR\CDS\Development\CDSDataCleanMacros.xls") xlx.Run "CDSDataCleanMacros.xls!CleanTradeandRefEntityData()" xlw.Close False xlx.Quit Set xlw = Nothing Set xlx = Nothing End Sub Thanks.. SJH Quote: > Here's some sample code for opening an EXCEL workbook and running a VBA > macro that's in the workbook in a public module: > Public Sub TestMacroRun() > Dim xlx As Object, xlw As Object > Set xlx = CreateObject("excel.application") > Set xlw = xlx.workbooks.Open("C:\Filename.xls") > xlx.Run "Filename.xls!MacroName()" > xlw.Close False > xlx.Quit > Set xlw = Nothing > Set xlx = Nothing > End Sub > -- > Ken Snell > <MS ACCESS MVP> > > Is there a way, via a command or another way, to open and run an Excel > macro > > stored in a workbook through Access???
Tue, 06 Dec 2005 02:54:22 GMT

Scott Holtzma #5 / 6	Run Excel Macro through Access VBA Found the answer.. the line " xlx.Run "CDSDataCleanMacros.xls!CleanTradeandRefEntityData()" " does not need the () in it.. works fine from there.. thanks a lot!!!! Quote: > Ken.. Thanks for this mock code. I have applied to my existing needs and > can not get past the xlx.Run statement. When the code gets to that point > it says: > Run-time error '1004': > The macro 'CDSDataCleanMacros.xls!CleanTradeandRefEntityData()' cannot be > found. > Do you possibly now what is wrong with this piece of the code given my code > below, based on your suggestion???? > Public Sub CleanDownloadedTradeDatainExcel() > Dim xlx As Object, xlw As Object > Set xlx = CreateObject("excel.application") > Set xlw = > xlx.Workbooks.Open("F:\GFIR\CDS\Development\CDSDataCleanMacros.xls") > xlx.Run "CDSDataCleanMacros.xls!CleanTradeandRefEntityData()" > xlw.Close False > xlx.Quit > Set xlw = Nothing > Set xlx = Nothing > End Sub > Thanks.. SJH > > Here's some sample code for opening an EXCEL workbook and running a VBA > > macro that's in the workbook in a public module: > > Public Sub TestMacroRun() > > Dim xlx As Object, xlw As Object > > Set xlx = CreateObject("excel.application") > > Set xlw = xlx.workbooks.Open("C:\Filename.xls") > > xlx.Run "Filename.xls!MacroName()" > > xlw.Close False > > xlx.Quit > > Set xlw = Nothing > > Set xlx = Nothing > > End Sub > > -- > > Ken Snell > > <MS ACCESS MVP> > > > Is there a way, via a command or another way, to open and run an Excel > > macro > > > stored in a workbook through Access???
Tue, 06 Dec 2005 05:14:49 GMT

Ken Snel #6 / 6	Run Excel Macro through Access VBA Glad you found the answer. Sorry about the (). I've not had a problem with that when I've used them. -- Ken Snell <MS ACCESS MVP> Quote: > Found the answer.. the line " xlx.Run > "CDSDataCleanMacros.xls!CleanTradeandRefEntityData()" " does not need the () > in it.. works fine from there.. thanks a lot!!!! > > Ken.. Thanks for this mock code. I have applied to my existing needs > and > > can not get past the xlx.Run statement. When the code gets to that point > > it says: > > Run-time error '1004': > > The macro 'CDSDataCleanMacros.xls!CleanTradeandRefEntityData()' cannot be > > found. > > Do you possibly now what is wrong with this piece of the code given my > code > > below, based on your suggestion???? > > Public Sub CleanDownloadedTradeDatainExcel() > > Dim xlx As Object, xlw As Object > > Set xlx = CreateObject("excel.application") > > Set xlw = > > xlx.Workbooks.Open("F:\GFIR\CDS\Development\CDSDataCleanMacros.xls") > > xlx.Run "CDSDataCleanMacros.xls!CleanTradeandRefEntityData()" > > xlw.Close False > > xlx.Quit > > Set xlw = Nothing > > Set xlx = Nothing > > End Sub > > Thanks.. SJH > > > Here's some sample code for opening an EXCEL workbook and running a VBA > > > macro that's in the workbook in a public module: > > > Public Sub TestMacroRun() > > > Dim xlx As Object, xlw As Object > > > Set xlx = CreateObject("excel.application") > > > Set xlw = xlx.workbooks.Open("C:\Filename.xls") > > > xlx.Run "Filename.xls!MacroName()" > > > xlw.Close False > > > xlx.Quit > > > Set xlw = Nothing > > > Set xlx = Nothing > > > End Sub > > > -- > > > Ken Snell > > > <MS ACCESS MVP> > > > > Is there a way, via a command or another way, to open and run an Excel > > > macro > > > > stored in a workbook through Access???
Tue, 06 Dec 2005 08:56:46 GMT

Page 1 of 1

[ 6 post ]