Hi all,
I was hoping that someone could tell me if it is possible to run an Excel macro from an Access database. I am importing 3 Excel worksheets into Access and this macro needs to be run on all 3 evertime that this file is imported by the user (twice a week). If this can be done could someone please tell me how ??
HI,
The following code will run a macro in Excel.
customise it to your needs:
Dim XL As Object
Set XL = CreateObject("excel.Application")
With XL.Application
.Visible = True
.workbooks.Open "C:\folderpath\your file.xls"
.run "your macro name"
.Quit
End With
Set XL = Nothing
Was this answer helpful ?
Yes No
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by Bizzle Thank you very much !!!! |
THIS IS WHAT I AM NEEDED TO DO BUT:
the Marco i am wanting to call is not housed/resides in the same excel workbook as it was in this example.
My situation is that I've created a toolbox button on excel that is link to a shared workbook located at C:\"holdsmacro.xls" where the macro is housed. Therefore, no matter what file I have open i can click on my icon an the macre called "macrofunction" will run.
so what i need to do is
open the file i need to run the macro in
open the file that houses the macro
then, set focus on the file i need to run the macro in (I THINK)
then run the macro
does that make sense? any advise is greatly appreciated. i've be struggling with this for a while.
tuk
Was this answer helpful ?
Yes No
Dim XL As Object
Set XL = CreateObject("excel.Application")
With XL.Application
.Visible = True
'open the file that has the macro
.workbooks.Open "C:\folderpath\your macro file.xls"
'then open the file to apply the macro to
.workbooks.Open "C:\folderpath\your macro target file.xls"
'that will leave the target file open and activiated then..
.run "your macro name"
'then you might want to save and close the active (target) workbook
.Activeworkbook.close savechanges:=true
'then the same command again will close and save your macro workbook
.Activeworkbook.close savechanges:=true
'then exit excel
.Quit
End With
'tidy up
Set XL = Nothing
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by meloncolly Dim XL As Object
Set XL = CreateObject("excel.Application")
With XL.Application
.Visible = True
'open the file that has the macro
.workbooks.Open "C:\folderpath\your macro file.xls"
'then open the file to apply the macro to
.workbooks.Open "C:\folderpath\your macro target file.xls"
'that will leave the target file open and activiated then..
.run "your macro name"
'then you might want to save and close the active (target) workbook
.Activeworkbook.close savechanges:=true
'then the same command again will close and save your macro workbook
.Activeworkbook.close savechanges:=true
'then exit excel
.Quit
End With
'tidy up
Set XL = Nothing |
thanks for the info...i am still working on it. that is, i get an run time error 1004......macro not found. the macro name is as followed in the error message: 'my macro name'
could it be the syntax on how i have the macro name? as is step through the code it open the file with the macro, which i what i want...so when i go to double check the macro name....in excel, tools macros to view the list of macros
it look like the following:
'my macro file.xls'!my macro name
therefore, do i need
.run 'my macro file.xls'!my macro name?
note there are no spaces in the macro name, but there are spaces in the macro file name, if that means anything.
thanks
tuk
Was this answer helpful ?
Yes No
no, you just need
.run "my macro name"
you will need to use the double quotation marks
Was this answer helpful ?
Yes No
ahhhhhhh----HHAAAAAAAA
i found out the problem....it was not locating the macro because the workbook that houses the macro was hidden in the excel file.
but i still need help because:
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden. i am trying to run this macro for code in ms access.
can anyone add to the coding above that Meloncolly provided to complete this function.
thanks for you help
Was this answer helpful ?
Yes No
please post the full code you're using
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by meloncolly please post the full code you're using |
here is the code i am using
Code:
Private Sub cmdImport_Click()
Dim appExcel As Excel.Application
Set appExcel = New Excel.Application
Dim strRange As String
Dim strReqNo As String
Dim Msg, Style, Title, Response, MyString
Msg = "Importing a file more than once will cause errors. Do you wish to proceed?" ' Define message.
Style = vbYesNo + vbExclamation + vbDefaultButton2 ' Define buttons.
Title = "Import single proposal" ' Define title.
' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
'this is the doc that i want to perform the macro on
Excel.Application.Workbooks.Open "C:\Combined Shorts Subledger CurrentTEST.xls"
Excel.Application.Visible = True
Call xlAddin2
ActiveWorkbook.Save
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_DELETE_PCSL_TRNX(UPLOAD)"
DoCmd.TransferSpreadsheet acImport, , "PCSL TRNX R3(UPLOAD)", _
"C:\Combined Shorts Subledger CurrentTEST.xls", True, "datapastedvalues"
ActiveWorkbook.Close
DoCmd.SetWarnings True
Excel.Application.Quit
MsgBox "Import was successful.", vbInformation
Else ' User chose No.
MyString = "No" ' Perform some action. None!
MsgBox "Import was cancelled.", vbInformation
End If
End Sub
Sub xlAddin2()
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
With objExcel.Application
.Visible = True
'this is the doc that contains the macro
.Workbooks.Open "C:\MSAccess_Subledger_Converter.xls"
.ActiveWindow.Activate = False
'this is the macro name
.Run "MSAccess_Subledger_Converter_Macro"
.Quit
End With
Set objExcel = Nothing
End Sub
thanks
TUK
Was this answer helpful ?
Yes No
