modifying excel from access

[Guest]

I have created the following macro in Excel and i wish to run it from a
command button in access.

could somebody show me what needs to be modified in the code for it to work
in a module.

Cheers.

Workbooks.Open Filename:= _
"U:\Technology\opsdelivery\Region Team Finance\PO Reports\Latest PO
Report - UK West.xls"
ChDir "C:\access info"
ActiveWorkbook.SaveAs Filename:="C:\access info\PO Report - UK
West.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets(Array("csr 1 to 5999", "csr 6000 to 12999", "csr 13000 to 20999", _
"csr 21000 and above")).Select
Sheets("csr 1 to 5999").Activate
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
Sheets("csr 1 to 5999").Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[pietlinden]

I have created the following macro in Excel and i wish to run it from a
command button in access.

could somebody show me what needs to be modified in the code for it to work
in a module.

Cheers.

Workbooks.Open Filename:= _
"U:\Technology\opsdelivery\Region Team Finance\PO Reports\Latest PO
Report - UK West.xls"
ChDir "C:\access info"
ActiveWorkbook.SaveAs Filename:="C:\access info\PO Report - UK
West.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets(Array("csr 1 to 5999", "csr 6000 to 12999", "csr 13000 to 20999", _
"csr 21000 and above")).Select
Sheets("csr 1 to 5999").Activate
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
Sheets("csr 1 to 5999").Select
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

basically, if you're going to use early binding, you need to add a
reference to the Excel library you're using (Under the Tools menu in a
Module window) and then, since you're not in Excel, but Access, you
need to declare an Excel application object and then probably a
worksheet. Then you'll have access to the full Excel object model.

There's an example of it here. http://www.mvps.org/access/modules/mdl0035.htm
see the Subroutine sCopyRSExample
toward the bottom of the page.