Macro to run an Existing Microsoft Access Macro
Hi - I am trying to automate the running of an Access Macro without going
into Access. The access macro using the Transferspreadsheet action. While the macro below works it has 2 problems: 1 - Since it's in the excel book that the Access macro is trying to update it waits until the Excel book is closed - hence is it possible to force Access to update excel that is already open. 2 - The Access macro opens a Db2 database which asks for a username / pasword, hence is it possible to pass the username / password from the Excel book from two cells? The macro in Excel that I am using is: Sub AccessTest1() Dim A As Object Set A = CreateObject("Access.Application") 'A.Visible = False 'I recommend this is remmed to begin with A.OpenCurrentDatabase ("C:\eESM Reporting\Equinox eESM Web Reporting.mdb") 'update with details of your .mdb file A.DoCmd.RunMacro "Web Extract" 'update with the name of your Access macro End Sub |
Macro to run an Existing Microsoft Access Macro
Try something like this:
Sub AccessTest1() Dim AccApp As Access.Application Set AccApp = CreateObject("Access.Application") 'A.Visible = False 'I recommend this is remmed to begin with AccApp.OpenCurrentDatabase ("C:\eESM Reporting\Equinox eESM Web Reporting.mdb") 'update with details of your .mdb file AccApp.DoCmd.RunMacro "Web Extract" 'update with the name of your Access macro AccApp.Quit End Sub Ensure that in the references, you've chosen the Microsoft Access Object Library. -- HTH, Barb Reinhardt "Paul Dennis" wrote: Hi - I am trying to automate the running of an Access Macro without going into Access. The access macro using the Transferspreadsheet action. While the macro below works it has 2 problems: 1 - Since it's in the excel book that the Access macro is trying to update it waits until the Excel book is closed - hence is it possible to force Access to update excel that is already open. 2 - The Access macro opens a Db2 database which asks for a username / pasword, hence is it possible to pass the username / password from the Excel book from two cells? The macro in Excel that I am using is: Sub AccessTest1() Dim A As Object Set A = CreateObject("Access.Application") 'A.Visible = False 'I recommend this is remmed to begin with A.OpenCurrentDatabase ("C:\eESM Reporting\Equinox eESM Web Reporting.mdb") 'update with details of your .mdb file A.DoCmd.RunMacro "Web Extract" 'update with the name of your Access macro End Sub |
All times are GMT +1. The time now is 12:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com