Transfering data from Access to Excel and then running an Excel macro

[jewart17]

I have a command button in Access 2007 which transfers data from a form to an Excel workbook. Once the data is transfered, I would like to run a macro in Excel which emails a copy of the workbook to a specified individual. The transfer from Access to Excel works properly, and the macro in Excel works properly, but I'd like to know if I can have both of these actions take place with the click of one button in Access. Here is the code for the On click event in Access:

Private Sub cmdExportSpecOrd_Click()
Dim strFile As String
Dim oXLS As Object
Dim oWkb As Object
strFile = "J:\Product Management\HP PurchasEdge\Special Orders\Database Templates\Special Order Form Template.xls"
Set oXLS = CreateObject("Excel.Application")
oXLS.Workbooks.Open strFile
Set oWkb = oXLS.Workbooks(1)
oWkb.Worksheets(1).Range("J20") = Me.txtCSRName
oWkb.Worksheets(1).Range("M20") = Me.txtCSRExt
oWkb.Worksheets(1).Range("I16") = Me.txtCustName
oWkb.Worksheets(1).Range("E18") = Me.txtCustName
oWkb.Worksheets(1).Range("K29") = Me.txtCustName
oWkb.Worksheets(1).Range("H18") = Me.txtPhoneNum
oWkb.Worksheets(1).Range("D20") = Me.txtOrdDate
oWkb.Worksheets(1).Range("F20") = Me.txtPO
oWkb.Worksheets(1).Range("L23") = Me.txtCompName
oWkb.Worksheets(1).Range("J25") = Me.txtAddress
oWkb.Worksheets(1).Range("J27") = Me.txtCity
oWkb.Worksheets(1).Range("M27") = Me.cboState
oWkb.Worksheets(1).Range("N27") = Me.txtZip
oWkb.Worksheets(1).Range("J31") = Me.txtCustEmail
oWkb.Worksheets(1).Range("C36") = Me.cboSKU
oWkb.Worksheets(1).Range("F36") = Me.txtProdDesc
oWkb.Worksheets(1).Range("I36") = Me.txtQty
oWkb.Worksheets(1).Range("K36") = Me.txtListPrice
oWkb.Worksheets(1).Range("M36") = Me.txtAzertyCost
oWkb.Worksheets(1).Range("O36") = Me.txtDealerCost
oWkb.Worksheets(1).Range("C37") = Me.cboSKU1
oWkb.Worksheets(1).Range("F37") = Me.txtProdDesc1
oWkb.Worksheets(1).Range("I37") = Me.txtQty1
oWkb.Worksheets(1).Range("K37") = Me.txtListPrice1
oWkb.Worksheets(1).Range("M37") = Me.txtAzertyCost1
oWkb.Worksheets(1).Range("O37") = Me.txtDealerCost1
oWkb.Worksheets(1).Range("C38") = Me.cboSKU2
oWkb.Worksheets(1).Range("F38") = Me.txtProdDesc2
oWkb.Worksheets(1).Range("I38") = Me.txtQty2
oWkb.Worksheets(1).Range("K38") = Me.txtListPrice2
oWkb.Worksheets(1).Range("M38") = Me.txtAzertyCost2
oWkb.Worksheets(1).Range("O38") = Me.txtDealerCost2
oWkb.Worksheets(1).Range("C39") = Me.cboSKU3
oWkb.Worksheets(1).Range("F39") = Me.txtProdDesc3
oWkb.Worksheets(1).Range("I39") = Me.txtQty3
oWkb.Worksheets(1).Range("K39") = Me.txtListPrice3
oWkb.Worksheets(1).Range("M39") = Me.txtAzertyCost3
oWkb.Worksheets(1).Range("O39") = Me.txtDealerCost3
oWkb.Worksheets(1).Range("C40") = Me.cboSKU4
oWkb.Worksheets(1).Range("F40") = Me.txtProdDesc4
oWkb.Worksheets(1).Range("I40") = Me.txtQty4
oWkb.Worksheets(1).Range("K40") = Me.txtListPrice4
oWkb.Worksheets(1).Range("M40") = Me.txtAzertyCost4
oWkb.Worksheets(1).Range("O40") = Me.txtDealerCost4

oXLS.Visible = True
Set oXLS = Nothing
Set oWkb = Nothing


End Sub

The macro name in the Excel workbook is "SpecialOrder" - is there a way to have that run at the end of the code listed above?

Thanks in advance for your help

 

[PHV]

...
oXLS.Visible = True
oXLS.Run "SpecialOrder"
Set oXLS = Nothing
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jewart17]

Thank you for the quick response. It is working (transfering data correctly and sending the email), but I keep getting:

Run-time error '440':

Automation error

When I DeBug, it takes me to this line of code:

oXLS.Run "SpecialOrder"

Any idea what is causing this?

 

[PHV]

So, what is exactly "SpecialOrder" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jewart17]

Here is the code from the macro:

Sub SpecialOrder()
'Working in 97-2010
Dim wb As Workbook
Dim I As Long

Set wb = ActiveWorkbook

ActiveSheet.Buttons("Button 1019").Visible = False
ActiveSheet.Buttons("Button 1018").Visible = False

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

On Error Resume Next
For I = 1 To 3
wb.SendMail "jjewart@ussco.com", _
Range("F20")
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0

ThisWorkbook.Close Saved = True

End Sub