Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Can an existing Macro in MS Access be triggered from an Excel Macro?
I am using Windows 98SE and Office 2000 (Excel and Access 2000). I tried recording an Excel Macro to open up an Access data base to trigger an existing Macro in Access. I got a message directing me to the Data drop down menu to do a query to get data. I just want to run the Macro, not do a query. Can I do what I hope to do or is it not possible? If it can be done, can you guide me to a sample of the proper code syntax? -- IHSWRKER |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello, the following worked for me:
Sub yadda() Dim accApp As Object Set accApp = GetObject(, "Access.Application") AppActivate "Microsoft Access" accApp.Run "foobar" Set accApp = Nothing End Sub There's another example and more on the Run Method in the Access VBE help file. Regards, Nate Oliver |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hey thanks for your response.
I assume foobar would be the name of the Macro!? I will give it a try as soon as I can get back to our church office. I'll let you know what happens. "Nate Oliver" wrote: Hello, the following worked for me: Sub yadda() Dim accApp As Object Set accApp = GetObject(, "Access.Application") AppActivate "Microsoft Access" accApp.Run "foobar" Set accApp = Nothing End Sub There's another example and more on the Run Method in the Access VBE help file. Regards, Nate Oliver |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
You are welcome.
Yes, in my example, foobar was the name of the Access VBA procedure (not an Access Macro per se). I meant to ask you about that... Good luck, if you need more help, post back. Regards, Nate Oliver |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hi Nate, I tried using the code as shown below:
I used your model and filled in what I thought ws intended. Although I have been trying to interpret the syntax, I am not doing well understanding what is required. The 4th line below failed (AppActivate . . .) and caused a message "Invalid procedure call or argument". Any suggestions? Sub Macro1() Dim accApp As Object Set accApp = GetObject("C:\Shared\Shared T&P\T&PFCC.mdb") AppActivate "Microsoft Access" accApp.Run "Macro1" Set accApp = Nothing End Sub "Nate Oliver" wrote: You are welcome. Yes, in my example, foobar was the name of the Access VBA procedure (not an Access Macro per se). I meant to ask you about that... Good luck, if you need more help, post back. Regards, Nate Oliver |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello again Don,
There's a difference between calling a VBA procedure in Access and a Macro in Access, they're two different things. To call a macro, see the following background info: http://msdn.microsoft.com/library/en...HV05186508.asp And the following example: Sub Macro1() Dim accApp As Object Set accApp = GetObject(, "Access.Application") AppActivate "Microsoft Access" accApp.docmd.runmacro "Macro1" Set accApp = Nothing End Sub Regards, Nate Oliver |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hi Nate,
Thanks again for your reply. I thought you gave up on me. Again, I'll try the RunMacro code you suggested, as soon as I can get back to the church. I'll let you know what I find. I appreciate you staying with me! Peace, Don "Nate Oliver" wrote: Hello again Don, There's a difference between calling a VBA procedure in Access and a Macro in Access, they're two different things. To call a macro, see the following background info: http://msdn.microsoft.com/library/en...HV05186508.asp And the following example: Sub Macro1() Dim accApp As Object Set accApp = GetObject(, "Access.Application") AppActivate "Microsoft Access" accApp.docmd.runmacro "Macro1" Set accApp = Nothing End Sub Regards, Nate Oliver |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello Don,
You're welcome. Sorry, I access this forum via: http://www.microsoft.com/office/comm...el.programming And it doesn't always sort (in most cases it does not) by last post, so I missed it. Also note, the only reason I was using: AppActivate "Microsoft Access Is because I was using a Msgbox in my test and I wanted to see it, you should not have to do this. Regards, Nate Oliver |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello Nate,
Thanks for the information. I am just thankfull that you are trying to help me. That's a big job since I have not really worked with Access or VBA coding before. I ran the following code: Sub Macro1() Dim accApp As Object Set accApp = GetObject("C:\Shared\Shared T&P\T&PFCC.mdb") accApp.docmd.runmacro "Macro1" Set accApp = Nothing End Sub I ran the above code and got the following message. Run time error "2501" The RunMacro action was cancelled. The RunMacro line was highlighted. Using F1 brought up a "Keyword not found" message. I stopped there, since I really don't understand what is wrong. One other question, on the "GetObject" line, if the run did not fail on that line, can I assume the path to the database syntax is correct? I am not sure if the .mdb extension should be included in the database name. Thank you once again for your patience, Don "Nate Oliver" wrote: Hello Don, You're welcome. Sorry, I access this forum via: http://www.microsoft.com/office/comm...el.programming And it doesn't always sort (in most cases it does not) by last post, so I missed it. Also note, the only reason I was using: AppActivate "Microsoft Access Is because I was using a Msgbox in my test and I wanted to see it, you should not have to do this. Regards, Nate Oliver |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello again Don,
Do you mind my asking what your macro does? I can see what it doesn't do: I just want to run the Macro, not do a query. The reason I ask is that your App might be more stable and maintainable if you keep your process in-house, in Excel. You can automate Access from Excel. Regards, Nate Oliver |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hi Nate,
I don't mind your asking, at all. Frankly I am more comfortable with Excel (not VBA or Access). The reason I made the comment (I just want to run the Macro, not do a query.) is that I had already created a simple Access query, follow on report and Macro. The Macro is just 2 steps, "Outputto" and "Quit". It outputs the report data to an Excel file. Since you asked, the only reason I go to Access is to use the report feature "hide repeating data". The report sort is by Day and Date and shows scheduled events in our church. The first event of each day shows the Day and Date and doesn't repeat for the other events of each day. The new Day and Date are much easier to identify. You can see the results at: http://www.firstchristianstow.org/ and select "Monthly Calendar" (Left side of screen). If you tell me this could be done in Excel, that would be the greatest. I would not go to Access at all. Logically, I believe it could, but I am not sure if there are formulas and/or functions capability in Excel, that would allow doing it. If you know it can be done and point me in the direction of what can be used to do it, I am definitly open to try it. Thanks once more for your help, Don "Nate Oliver" wrote: Hello again Don, Do you mind my asking what your macro does? I can see what it doesn't do: I just want to run the Macro, not do a query. The reason I ask is that your App might be more stable and maintainable if you keep your process in-house, in Excel. You can automate Access from Excel. Regards, Nate Oliver |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello again Don,
You should be able to do this with VBA, I'll provide a couple of examples. The first one is make a copy of Recordset, e.g., http://www.able-consulting.com/ADO_Faq.htm#Q42 For this you'll need a reference to Microsoft ActiveX Data Objects 2.5+ Library (my system jumps from 2.1 to 2.5 and ADODB.Stream isn't supported in 2.1, not sure when it was introduced): '-------------- Sub GrbAccessData1() Dim cn As ADODB.Connection Dim oRs1 As ADODB.Recordset Dim oStm As ADODB.Stream Dim oRs2 As ADODB.Recordset Dim lstField As Date Dim I As Integer Const dbFullname As String = "P:\DATA\test.mdb" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & dbFullname & ";" Set oRs1 = New ADODB.Recordset With oRs1 .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _ cn, adOpenStatic, adLockReadOnly Set oStm = New ADODB.Stream oStm.Open .Save oStm, adPersistXML .Close End With Set oRs1 = Nothing cn.Close: Set cn = Nothing Set oRs2 = New ADODB.Recordset With oRs2 .Open oStm, , , adLockOptimistic oStm.Close: Set oStm = Nothing If Not .EOF Then Let lstField = .Fields(1).Value .MoveNext For I = 2 To .RecordCount If .Fields(1).Value = lstField Then .Fields(0).Value = Null .Fields(1).Value = Null Else: lstField = .Fields(1).Value End If .MoveNext Next .MoveFirst: .Update End If End With Sheets(1).Range("a1").CopyFromRecordset oRs2 oRs2.Close: Set oRs2 = Nothing End Sub '-------------- You'd want to change your DB filepath & name and the sql you're passing, i.e., change: .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]" Approach 2 didn't attempt to create another recordset, it simply used an array: '-------------- Sub GrbAccessData2() Dim cn As ADODB.Connection Dim oRs1 As ADODB.Recordset Dim myArr() As Variant Dim lstField As Date Dim I As Integer Const dbFullname As String = "P:\DATA\test.mdb" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & dbFullname & ";" Set oRs1 = New ADODB.Recordset With oRs1 .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _ cn, adOpenStatic, adLockReadOnly Let myArr = oRs1.GetRows .Close End With Set oRs1 = Nothing cn.Close: Set cn = Nothing myArr = WorksheetFunction.Transpose(myArr) Let lstField = myArr(1, 2) For I = 2 To UBound(myArr, 1) If myArr(I, 2) = lstField Then myArr(I, 1) = Null myArr(I, 2) = Null Else: lstField = myArr(I, 2) End If Next Sheets(2).Range("a1").Resize( _ UBound(myArr, 1), UBound(myArr, 2)).Value = myArr End Sub '-------------- Again, you'll need a reference to ADO. Both are dynamic. GrbAccessData2 is about 10% faster in my tests. But, I'm using Excel's Transpose() function to flip the array in that example, and if you're using Excel 2000-, Transpose will have a fit if the array is holding more than 5,461 elements. Regards, Nate |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Oh Nate,
So far my experience with VBA has not been good, because I am so inept with it. The examples look like an even more sofisticated use of VBA. Wish me luck! I don't know if we have Microsoft ActiveX or ADO on our church PC (or at home). Does the statement Sub GrbAccessData1() infer that these examples are going after MS Access Data? Also, if Access is not required, I may be able to experiment with this at home first. I assume the examples are accomplishing the elimination of the repeating data in DAY and DATE. If so, I would not need to go after Access data. Can I assume the examples are not dependant on using Access (syntax, etc)? Is my first impression correct, that example 2 would be less complicated to impliment? Sorry to be so inexperienced with VBA and such. I don't even have a book, but trying to get one at the library. I hope I can pick up enough to impliment your examples. Thanks again, Don "Nate Oliver" wrote: Hello again Don, You should be able to do this with VBA, I'll provide a couple of examples. The first one is make a copy of Recordset, e.g., http://www.able-consulting.com/ADO_Faq.htm#Q42 For this you'll need a reference to Microsoft ActiveX Data Objects 2.5+ Library (my system jumps from 2.1 to 2.5 and ADODB.Stream isn't supported in 2.1, not sure when it was introduced): '-------------- Sub GrbAccessData1() Dim cn As ADODB.Connection Dim oRs1 As ADODB.Recordset Dim oStm As ADODB.Stream Dim oRs2 As ADODB.Recordset Dim lstField As Date Dim I As Integer Const dbFullname As String = "P:\DATA\test.mdb" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & dbFullname & ";" Set oRs1 = New ADODB.Recordset With oRs1 .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _ cn, adOpenStatic, adLockReadOnly Set oStm = New ADODB.Stream oStm.Open .Save oStm, adPersistXML .Close End With Set oRs1 = Nothing cn.Close: Set cn = Nothing Set oRs2 = New ADODB.Recordset With oRs2 .Open oStm, , , adLockOptimistic oStm.Close: Set oStm = Nothing If Not .EOF Then Let lstField = .Fields(1).Value .MoveNext For I = 2 To .RecordCount If .Fields(1).Value = lstField Then .Fields(0).Value = Null .Fields(1).Value = Null Else: lstField = .Fields(1).Value End If .MoveNext Next .MoveFirst: .Update End If End With Sheets(1).Range("a1").CopyFromRecordset oRs2 oRs2.Close: Set oRs2 = Nothing End Sub '-------------- You'd want to change your DB filepath & name and the sql you're passing, i.e., change: .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]" Approach 2 didn't attempt to create another recordset, it simply used an array: '-------------- Sub GrbAccessData2() Dim cn As ADODB.Connection Dim oRs1 As ADODB.Recordset Dim myArr() As Variant Dim lstField As Date Dim I As Integer Const dbFullname As String = "P:\DATA\test.mdb" Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & dbFullname & ";" Set oRs1 = New ADODB.Recordset With oRs1 .Open "Select [DAY], [DATE_F], [EVENT NAME], [LOC], [START], [END] " & _ "From [Events] Where Month([Date_F])=3 Order By [DATE_F]", _ cn, adOpenStatic, adLockReadOnly Let myArr = oRs1.GetRows .Close End With Set oRs1 = Nothing cn.Close: Set cn = Nothing myArr = WorksheetFunction.Transpose(myArr) Let lstField = myArr(1, 2) For I = 2 To UBound(myArr, 1) If myArr(I, 2) = lstField Then myArr(I, 1) = Null myArr(I, 2) = Null Else: lstField = myArr(I, 2) End If Next Sheets(2).Range("a1").Resize( _ UBound(myArr, 1), UBound(myArr, 2)).Value = myArr End Sub '-------------- Again, you'll need a reference to ADO. Both are dynamic. GrbAccessData2 is about 10% faster in my tests. But, I'm using Excel's Transpose() function to flip the array in that example, and if you're using Excel 2000-, Transpose will have a fit if the array is holding more than 5,461 elements. Regards, Nate |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trigger a Macro in MS Access from an Excel Macro?
Hello Don,
Error 2501 stems from the system not being able to find the table or query or database that you specified. It is also a symptom of a corrupted database. Just a thought, but have you tried removing the ampersand "&" character and blank spaces from your path/file names? Example - change: "C:\Shared\Shared T&P\T&PFCC.mdb" to "C:\Shared\Shared_TP\TPFCC.mdb" Also, check the file size of your database. If it is more than a couple of megabytes in size, then you should think of a ways to clean it up and remove unecessary tables, queries and reports. Finally, run 'Compact & Repair' from the 'Tools - Database Utilities' drop-down menu to fix any broken links. Good luck! Joshua *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
Macro trigger? | Excel Programming | |||
Macro trigger? | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming | |||
EXCEL: trigger web query from macro? | Excel Programming |