Run Excel macro from Access VBA

[ragsgold]

I got this to work.


Code in Excel general module:

Sub myMacro()
Worksheets("Sheet1").Range("A1") = "Start"
MsgBox "Done"
Worksheets("Sheet1").Range("A1") = "Done"
End Sub


Code in Access general module:

Public Sub elxwrk()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open ("C:\Temp\Book1.xlsm")
oExcel.Run "myMacro"
Do While Not oExcel.Worksheets("Sheet1").Range("A1") = "Done"
DoEvents
Loop
oExcel.Worksheets("Sheet1").Range("A1") = "Start"
oExcel.Quit
End Sub

Ok here i what I did or changed

in my Excel

Sub myMacro()
Worksheets("Pivot").Range("D1") = "Start"
ActiveWorkbook.refreshall
MsgBox "Done"
Worksheets("Pivot").Range("D1") = "Done"
End Sub

the above works very well no issues.

Next in my Access Module

Public Sub elxwrk()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open ("Y:\nara\Access DB Projects\Infinium Actual payroll DB\Reports\Infinium Payroll Report1.xlsm")
oExcel.Run "myMacro"
Do While Not oExcel.Worksheets("pivot").Range("D1") = "Done"
DoEvents
Loop
oExcel.Worksheets("pivot").Range("D1") = "Start"
oExcel.Quit
End Sub

when i execute it i get an error at line - Public Sub elxwrk() "compile error user defined type not defined"

what do you think i did incorrectly in the Access code here ?

[June7]

Have you set a VBA reference in Access for Excel 12.0 Object Library?

[ragsgold]

Have you set a VBA reference in Acces for Excel 12.0 Object Library?

I havnt set anything like that i could remember of where can i check that ...do you mean in the ODBC driver or Regedit ? But i have other access databases where I am using VBA codes interfacing excel is this file specific

[June7]

Yes, it is file specific. From the VBA editor select Tools>References>scroll until you find the library and check it:

Microsoft Excel 12.0 Object Library


The MsgBox in the Excel code is not necessary. I just used it as a debug/testing tool.

[alcapps]

no problem.. I hope that works for you..

[ragsgold]

Yes, it is file specific. From the VBA editor select Tools>References>scroll until you find the library and check it:

Microsoft Excel 12.0 Object Library


The MsgBox in the Excel code is not necessary. I just used it as a debug/testing tool.

Yes i just checked and it is selected or checked

[June7]

The code works for me. If you want us to analyze your effort then provide files. Follow instructions at bottom of my post.

I have no idea if something might be different for Windows 7 64 bit Access 2010 64 bit.

[ragsgold]

The code works for me. If you want us to analyze your effort then provide files. Follow instructions at bottom of my post.

I have no idea if something might be different for Windows 7 64 bit Access 2010 64 bit.

I actually have Access 2007 and Windows XP SP2 at work. i guess its 32 bit. Will try and attach access and excel file per the instructuions

[ragsgold]

Hi June 7,

Attached are the files. Just rename the files by removing the "copy of" prefix.

Some info about the files.

MS Accsss - input File - linked excel file feeds to Access database

Copy of Infinium Payroll Report1 - XLSM file that has a pivot & data table pulling data from the Access DB table - this is what needs to be refreshed.

Copy of Infinium payroll Table Data - is Access 2007 DB

Awaiting your analysis

[June7]

The Excel library was not selected in Access VBA. I reset the Access link to worksheet and also the workbook link to Access. Also created dummy records. The code runs without error but leaves an instance of Excel running in Windows Task Manager and data does not refresh. Manually running Refresh from ribbon or the macro does do the refresh. I added one line to the Access procedure and then it all worked:

oExcel.ActiveWorkbook.Close (True)
oExcel.Quit

Why are you using Excel to report data instead of Access report object?

[ragsgold]

The Excel library was not selected in Access VBA. I reset the Access link to worksheet and also the workbook link to Access. Also created dummy records. The code runs without error but leaves an instance of Excel running in Windows Task Manager and data does not refresh. Manually running Refresh from ribbon or the macro does do the refresh. I added one line to the Access procedure and then it all worked:

oExcel.ActiveWorkbook.Close (True)
oExcel.Quit

Why are you using Excel to report data instead of Access report object?

Umm I see Microsoft Office 12.0 Access database Engine object Library "checked" and now checked the 12 excel Library. I included the code line you added. Now it runs without errors but its still not refreshing the excel. I get done pop message when i go to the excel its not refreshed.

I use Excel because that report file will be emailed to a specific group of people who has a requirement to view it as pivot and data in two sheets. By Access report object you mean ? Access Reports? is it possible to do the same via access ?

[June7]

I will test again tonight but it was refreshing the Excel in my tests last night.

Yes, I expect it could be replicated in Access. Probably need a subreport. If the people only need to view the data and not manipulate it, personally I would move mountain to eliminate the Excel component.

[ragsgold]

I will test again tonight but it was refreshing the Excel in my tests last night.

Yes, I expect it could be replicated in Access. Probably need a subreport. If the people only need to view the data and not manipulate it, personally I would move mountain to eliminate the Excel component.

I just wanted to share this before you test

My access code as below

Public Function datexrefr()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open ("Y:\Raghav\Access DB Projects\Infinium Actual payroll DB\Reports\Infinium Payroll Report1.xlsm")
oExcel.Run "module4.myMacro"
Do While Not oExcel.Worksheets("pivot").Range("D1") = "Done"
DoEvents
Loop
oExcel.Worksheets("pivot").Range("D1") = "Start"
oExcel.ActiveWorkbook.Close (True)
oExcel.Quit
End Function

[June7]

Does the Function work? Why make it a Function instead of Sub?

I tested the file again and it works for me. I am at a loss as to what is going wrong for you.

[ragsgold]

Does the Function work? Why make it a Function instead of Sub?

I tested the file again and it works for me. I am at a loss as to what is going wrong for you.

Code works without errors but, the problem is it doesnt refresh the excel file .

in the mean time i figured out a traditional work around without codes. i created a macro with transfer spreadsheet and dumped data in sheet 2 and in sheet 1 i built a pivot based on sheet 2 in excel and set it to refresh on open.

so macro runs dumps or over writes file and sheet and i created another line in the macro with Run app to open the excel that made the pivot to refresh. This is not 100% what i wanted but gets close to what i need. I have no clue why the code doesn't work for me