Envoke excel macro fr Access

What code would be appropriate to open an excel file, run a macro in excel and close excel, all
from access?

Please help.
Thanks.

Hi,

I just completed a macro that runs Excel from an Access macro. I am using this macro to import data from a spreadsheet into an Access table. All pertinent macro code is in red.The actual command that performs the action of opening Excel is called "Excel.Application.Workbooks.Open". You should read up on this further. The next line defines if the user sees the file onscreen or not, meaning that you can run routines in Excel from a macro resident in Access without the user even seeing what's going on. In my case, this was very handy. Just be careful, since macro syntax for Excel might change a little when the macro resides in an Access module.

Also, be careful with this process. I am having a problem unloading the created instance of Excel from resident memory. Even if I use the command "Excel.Application.Quit", Excel remains in resident memory. I hope all this helps you.

Creo

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.
Excel.Application.Workbooks.Open "C:\EmailQuote\qryExportQuote.xls"
Excel.Application.Visible = False
Worksheets(1).Select
Sheets(1).Name = "Header"
Sheets("Header").Select
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Move After:=Sheets(2)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "ItemDetails"
ActiveSheet.Previous.Select
Columns("A:F").Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Previous.Select
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.Range("A1", Selection.End(xlDown)).Offset(2, 0).Select
Selection.EntireRow.Delete Shift:=xlUp
Columns("C:H").Select
Selection.NumberFormat = "@"
Columns("K:K").Select
Selection.NumberFormat = "@"
Columns("O:O").Select
Selection.NumberFormat = "@"
Columns("L:M").Select
Selection.NumberFormat = "m/d/yyyy"
strRange = Range("A1", Selection.End(xlDown).End(xlToRight)).Select
Columns("A:A").Select
Selection.Delete
ActiveWorkbook.Names.Add Name:="Header", RefersTo:="=Header!$A$1:$N$2"
ActiveWorkbook.Names.Add Name:="ItemDetails", RefersTo:="=ItemDetails!$A$1:$F$3000"
ActiveWorkbook.Names.Add Name:="ReqNoDetails", RefersTo:="=ItemDetails!$A$1:$a$2"
Range("A1").Select
ActiveWorkbook.Save
DoCmd.TransferSpreadsheet acImport, , "tblInputHeader", _
"c:\EmailQuote\qryExportQuote.xls", True, "Header"
ActiveWorkbook.Close
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMaxReqNo"
DoCmd.TransferSpreadsheet acExport, , "tblMaxReqNo", _
"C:\EmailQuote\qryReqNo.xls", True
Workbooks.Open "C:\EmailQuote\qryExportQuote.xls"
Workbooks.Open "C:\EmailQuote\qryReqNo.xls"
Range("A2").Select
Selection.Copy
Windows("qryExportQuote.xls").Activate
ActiveSheet.Next.Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWorkbook.Close
DoCmd.TransferSpreadsheet acImport, , "tblInputItemDetail", _
"c:\EmailQuote\qryExportQuote.xls", True, "ItemDetails"
DoCmd.OpenQuery "qryDeleteBlanksInputDetail"
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

I am attemting to tweek the previous coding to use in my ms access datebase....can anyone locate where i am going wrong? My error is on the DoCmd.TransferSpreadsheet commard. It cannot locate the newly created worksheet in the excel file.

Your assistance is greatly appreciated.
TukTuk

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.
Excel.Application.Workbooks.Open "C:\tuktuk\import.xls"
Excel.Application.Visible = False

'VBA code from MS Excel Macro

ActiveSheet.Next.Select
Sheets.Add
ActiveSheet.Next.Select
Selection.Copy
ActiveSheet.Previous.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Name = "PCSL TRNX R3(IMPORT)"
Range("A1").Select

'VBA in access errors here, but runs past if removed (but then cannot file the newly created worksheet in MS Excel)
Application.CutCopyMode = False

ActiveWorkbook.Save

DoCmd.OpenQuery "qry_DELETE_PCSL_TRNX(UPLOAD)"
DoCmd.TransferSpreadsheet acImport, , "PCSL TRNX R3(UPLOAD)", _
"C:\tuktuk\import.xls", True, "PCSL TRNX R3(IMPORT)"
ActiveWorkbook.Close
DoCmd.SetWarnings False
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