Run Excel macro from Access and reference field on Access form. 1

[ajaeger]

I have a macro written in Excel that runs against all the files in a particular folder. I have the folder name hard coded in the macro. Right now, I can run it from Excel without problems. However, as this is just one of many steps in getting to the end result, I have created an Access file that will automate all the steps. From my form (frmExport) I want to be able to run this Excel macro against all files in the folder referenced on frmExport (frmExport.Path) instead of running the macro against all files in the folder that is hard coded in the macro.

This is the code I have in Access. It calls the Excel macro O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls is the Excel file that contains the macro that I want to run:

Private Sub cmdExcelMacro_Click()

Dim xlApp As Object
Dim wb As Object
Dim ws As Object
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open("O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls", False, False)
xlApp.Visible = True
xlApp.Run "'O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls'!RunsMacroAgainstAll"
wb.Close (False)
xlApp.Quit
Set xlApp = Nothing

End Sub

Here's the code I have in the Excel macro:

Sub RunsMacroAgainstAll()

Dim FSO As FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Dim xlWB As Workbook
Dim strPath As String

strPath = forms!frmExport.path
This brings up an error as the Access reference is not recognized in Excel

Set FSO = New FileSystemObject
Set myFolder = FSO.GetFolder(" & strPath & ")
For Each myFile In myFolder.Files
Set xlWB = Application.Workbooks.Open(myFile)

'*****************
blah, blah, blah, blah
'*****************

ActiveWorkbook.Save
ActiveWorkbook.Close

Next
Set myFolder = Nothing
Set FSO = Nothing

End Sub

In Access, I'm clicking cmdExport on frmExport to run the above code. Is there a way to pass the value of frmExport.PATH to the Excel macro so that it knows what folder to run against?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[PHV]

If you don't want to use an argument in your excel procedure you may consider the ChDir instruction in access and the CurDir function in excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ajaeger]

I think I'm a step closer, but not quite there yet. I have code in Access to export files in a .xls format to a directory that's specified on the Access form where the command button (cmdExportMacro) resides. Then it references the .xls file that contains the macro that I want run. This part works. However, the code in the Excel macro doesn't work. I need it to reference the path that the .xls files where saved to (in the Access code, it's Forms!frmExport.PATH, which I assign to strPath, which is then assigned to the strCurrent Directory, which is then assigned as the current directory using ChDir.)

Although I use the ChDir command in the Access code, it doesn't seem to carry over to the CurDir command that I used in the Excel code. I get the message "cur dir is f:/common" (from my MsgBox command line below)

Access Code that exports files and calls Excel Macro that *should* run against the exported files:

Private Sub cmdExportMacro_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim stDocName As String
Dim stLinkCriteria As String
Dim strQuery As String
Dim strExportAs As String
Dim strCompany As String
Dim strID As String
Dim strPath As String
Dim strCurrentDirectory As String

DoCmd.SetWarnings False

Set db = CurrentDb

Set rs = db.OpenRecordset("Select ID, COMPANY from tblMO_IDs")
Do Until rs.EOF = True

strCompany = rs!Company
strID = rs!ID

strQuery = "SELECT rtrim(dbo_vMOProfileExport.CATEGORY_1), rtrim(dbo_vMOProfileExport.CATEGORY_2), " & _
"rtrim(dbo_vMOProfileExport.CATEGORY_3), " & _
"rtrim(dbo_vMOProfileExport.FULL_NAME), rtrim(dbo_vMOProfileExport.TITLE), " & _
"rtrim(dbo_vMOProfileExport.COMPANY), rtrim(dbo_vMOProfileExport.ADDRESS_1), " & _
"rtrim(dbo_vMOProfileExport.ADDRESS_2), rtrim(dbo_vMOProfileExport.CITY), " & _
"rtrim(dbo_vMOProfileExport.STATE_PROVINCE), rtrim(dbo_vMOProfileExport.ZIP), " & _
"rtrim(dbo_vMOProfileExport.WORK_PHONE), rtrim(dbo_vMOProfileExport.EMAIL), " & _
"rtrim(dbo_vMOProfileExport.WEBSITE), rtrim(dbo_vMOProfileExport.CO_ID_PRINT) FROM dbo_vMOProfileExport WHERE " & _
"(dbo_vMOProfileExport.CO_ID) = '" & strID & "' ORDER BY " & _
"dbo_vMOProfileExport.SEQN"

Set qdf = db.QueryDefs("qryExportOne")
qdf.sql = strQuery

On Error GoTo Err_CantExport

strPath = Forms!frmExport.PATH

strCurrentDirectory = strPath

ChDir strCurrentDirectory

strExportAs = strPath + "\" + strCompany + ".xls"

DoCmd.SetWarnings False
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryExportOne", strExportAs
DoCmd.SetWarnings True

rs.MoveNext

Loop

'*************
'Start Excel Macro

Dim xlApp As Object
Dim wb As Object
Dim ws As Object
Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open("O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls", False, False)
xlApp.Visible = True
xlApp.Run "'O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls'!RunsMacroAgainstAll"
wb.Close (False)
xlApp.Quit
Set xlApp = Nothing

'End Excel Macro
'*************
End Sub

Excel Macro Code:

Sub RunsMacroAgainstAll()

'Add a reference to the Microsoft Scripting Runtime
Dim FSO As FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Dim xlWB As Workbook
Dim strPath As String

strPath = CurDir

MsgBox "cur dir is " & strPath

Set FSO = New FileSystemObject
Set myFolder = FSO.GetFolder(strPath)
For Each myFile In myFolder.Files
Set xlWB = Application.Workbooks.Open(myFile)
Rest of the code follows

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[PHV]

Seems you have current drive issue.
Why not using a parameter in your excel sub ?
In access:
xlApp.Run "'O:\Program Files\iMIS_SQL\custrpts\MOMacro.xls'!RunsMacroAgainstAll", strPath
In excel:
Sub RunsMacroAgainstAll(strPath As String)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ajaeger]

Thanks PH, you're a lifesaver once again!

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com