How do I get Access to run this Excel macro?

[Guest]

I want Access to run this macro dealing with excel but run it through access.
Where to I start?

Here is the Excel Macro.

Sub CompressPN()
'
' CompressPN Macro
' Macro recorded by E127232 (James Kendall)
'

'
Workbooks.Open Filename:= _
"N:\@Quality\Packaging Audits\COO Database\Mainframe Order
Multiples.xls"
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

Any help would be great.

 

[Guest]

I think there is a more direct approach, but here is one that I have used
successfully. Put your macro in spreadsheet #1 as an autoexec macro, but have
the macro modify spreadsheet #2. Call spreadsheet #1 from Access. More
specifically:

Place your macro in one Excel .xls and name it (the macro) Auto_Open so that
it runs automatically when that .xls is opened. Make sure to end it by
closing the open .xls (itself). Have the macro perform its actions on the
other .xls, though, not on itself (i.e. open and modify the "live" .xls file,
leaving itself unedited).

In Access, shell out to Excel like this:

Dim OpenExcel As Variant
OpenExcel = Shell("Excel.exe " & "C:\<path>\<name of your workbook having
the Auto_Open macro goes here>, 0)

Now, when Access hits this line, it opens the .xls containing the Auto_Open
macro, which, in turn, performs the actions you specify on the live .xls
file, and finally closes either the autoexec-related spreadsheet, or even
Excel completely.

 

[Guest]

The above method will work (auto run on open) but if you wanted more control
(pass variables) and be able to run more than one macro per spreadsheet you
could try:-

Dim XL As Object
Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open "C:\Macros.xls" ' open workbook containing formatting macros

XL.Run("Formatting.FormatFax")

' assumes a Module in excel named "Formatting" containing a procedure named
"FormatFax" - If your procedure in excel accepts arguments then you can add
them to this line in the brackets after the macro to be run (separated by
commas)
' This method (with slight amendments) would also allow you to return values
to access from yourmacro

 

[Guest]

Ok. I have the Auto_Open macro working (the easy part).
I know nothing about how to "shell out to Excel" and where I do that. Do I
write a Module or what? This is the portion I am trying to get my feet wet
in. Your help would be appreciated.

Also you mentioned that Access closes the spreadsheet automatically. Is
there code for that or does it do that automatically after giving the
directions from Access?

 

[Guest]

Thanks for the help. After a while I was able to get it to work. This is
what I used.

Function Excel_Formating_Data()

Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open "N:\@Quality\Packaging Audits\COO
Database\Mainframe Part Label Database.xls"
OpenExcel.Visible = True
OpenExcel.Run ("SortandFormatData")
OpenExcel.Workbooks.Close

End Function

 

[cwmoss]

This code is just what I have been trying to do.

Though I tried your code and substitued my file names. The excel file opens and the macro actually runs and complete its task. But upon excel file closing access errors with runtime 1004, stating that it could not find my excel macro even though it did run successfully.

Do you have an idea of what may case this?

Function Excel_Formating_Data()
Dim OpenExcel As Object
Set OpenExcel = CreateObject("Excel.Application")
OpenExcel.Workbooks.Open "C:\Data\global.xls"
OpenExcel.Visible = True
OpenExcel.Run ("MacroGlobalDefinitionsClean")
OpenExcel.Workbooks.Close
End Function

Thanks