Run Excel macro from Access VBA

[ragsgold]

Hello All,

I am trying to open a excel file from access - refresh the data in the excel file - save & close.

in my excel file i have the below macro

[Sub mymac()
'
' mymac Macro
ActiveWorkbook.refreshall
End Sub]

In my Access i have the below VBA module

Public Function RunExcelMacro()

[Dim xl As Object

'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("Y:\nara\Access DB Projects\Infinium Actual payroll DB\Reports\Infinium Payroll Report1.xlsm")

'Step 2: Make Excel visible
xl.Visible = False



'Step 3: Run the target macro
xl.Run "mymac"


'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

'Step 5: Memory Clean up.
Set xl = Nothing

End Function]
--------------------------------
when i execute my access code it runs - opens the excel file, saves and closes it doesnt refresh the macro i am referring to in bold which is none other than my excel macro.

Can you please help

[June7]

Does this help http://www.ehow.com/how_4595711_run-...el-access.html

Is the code in a module or behind a worksheet?

Have you manually run the Excel macro - are you sure it works?

[alcapps]

what I found is you must set some cell in the spreadsheet to a value and have your code wait till the value is present to do the close.
access will execute the macro and then close before the macro is finished.
so in your excel macro set a cell some where to nothing. and Have the excel macro change it to "Done" when the excel macro is complete.
in access have it wait for the cell to have "done" in it.
this way excel will finish the macro and access will close excel only when your macro is complete.



'Step 3: Run the target macro
xl.Run "mymac"


'Add this code and point to the excel cell you want to use.
do while not (excel cell) = "Done"
doevents
loop

'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

[ragsgold]

Does this help http://www.ehow.com/how_4595711_run-...el-access.html

Is the code in a module or behind a worksheet?

Have you manually run the Excel macro - are you sure it works?

Hi June7

Its in a module but, i tried copying it to a work sheet also then also it dint work. Yes i ran macro manually by opening my excel it still works . It just doesnt work when i try to execute it through access

[June7]

Did you try alcapps suggestion? Seems reasonable to me.

[ragsgold]

what I found is you must set some cell in the spreadsheet to a value and have your code wait till the value is present to do the close.
access will execute the macro and then close before the macro is finished.
so in your excel macro set a cell some where to nothing. and Have the excel macro change it to "Done" when the excel macro is complete.
in access have it wait for the cell to have "done" in it.
this way excel will finish the macro and access will close excel only when your macro is complete.



'Step 3: Run the target macro
xl.Run "mymac"


'Add this code and point to the excel cell you want to use.
do while not (excel cell) = "Done"
doevents
loop

'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

Hi alcapps

I thing i am missing some component on the "nothing" to "done" suggestion. i recreated the macro below. when i run the macro in excel manually it refreshes and where ever my cell pointer remains it adds the word done there. So if i rant it twice once from A1 and then from B1 it leaves a word "done". so the word "done" remains in that cell assuming i ran the Access code to look for "done" it will always see the word "done" from prior run and do the same old thing is it not ? I am sorry i am missing something

Sub mymac()
'
' mymac Macro
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "nothing"
Range("I1").Select
ActiveWorkbook.refreshall
ActiveCell.FormulaR1C1 = "done"
Range("I1").Select
End Sub

[ragsgold]

Did you try alcapps suggestion? Seems reasonable to me.

Thanks June7

yes I am working on alcapps suggestion and got stuck at the excel macro logic

[June7]

Pick a cell that would otherwise always be blank and reference it explicitely. Something like:

Worksheets("Sheet1").Range("A1").Value = "Start"
ActiveWorkbook.refreshall
Worksheets("Sheet1").Range("A1").Value = "Done"

Then code in the Access procedure:

do while xl.Worksheets("Sheet1").Range("A1") <> "Done"
doevents
loop

[ragsgold]

Does this help http://www.ehow.com/how_4595711_run-...el-access.html

Is the code in a module or behind a worksheet?

Have you manually run the Excel macro - are you sure it works?

I read through the link from your message and created the below in my access module and i left the word "done" in cell I1 i used Alcapps idea also into this code below. i get an error message now "Run time error 1004" " cannot run macro mymac.the macro may not be available in the workbook or macros are disabled" . i went and checked my excel and the macro exists and i had enabled all macros in excel options and checked the VBA thing also

Public Function elxwrk()
Set apExcel = CreateObject("Excel.Application")
apExcel.Visible = False
Set apExcelsheet = GetObject("Y:\Raghav\Access DB Projects\Infinium Actual payroll DB\Reports\Infinium Payroll Report1.xlsm")
apExcel.Application.Run "mymac"
'Add this code and point to the excel cell you want to use.
Do While Not (I1) = "Done"
DoEvents
Loop

'Step 4: Close and save the workbook, then close Excel
apExcel.ActiveWorkbook.Close (True)
apExcel.Quit
End Function

[ragsgold]

Alcapps & June7,

I missed to mention one piece of information am not sure whether its crucial. the excel contains a pivot table and data table (data lines) that is linked to the same access database (via external data) from where i am running these code's in my initial design,except to my reply to June7 to his link i created a test database with just the code. Before doing this i tried to create a pivot view in excel and tried exporting that but, it did export the data but not the pivot properly per design in the Access. Hence i changed it from sending from Access to excel to excel pulling data from Access but i have to refresh the excel file now !.

I also tried runcommand - action macro in access "exportpivottabletoexcel" but i did not know the correct parametres so that dint work either

[alcapps]

vba will not understand I1
you will need to reference a cell diferently for this to work.
like june7 suggested...apExcel.Worksheets("Sheet1").Range("A1 ")

Do While Not apExcel.Worksheets("Sheet1").Range("A1") = "Done"
DoEvents
Loop
use any valid cell .. a single cell on your spreadsheet ... in the case we use the first cell on the first row.
change out sheet1 if that is not the name of your sheet.
also change A1 to I1 if you want that to be the cell that you check.

[alcapps]

I meant that Nothing should be a blank cell as to say "" a blank cell in excel anywhere on any spreadsheet as long as it is not writen too until your macro is complete. and in that cell put Done without quotes. then access will look for the Done and then close down excel.

[June7]

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

[alcapps]

yes June7 that should do it.. that is what I was trying to communicate...

[ragsgold]

yes June7 that should do it.. that is what I was trying to communicate...

I am sorry i misunderstood