Access-Excel Automation

[Rafi]

What is the proper syntax for calling Excel from within Access and returning
an acknowledgment that Excel actually successfully completed a task?

The code below works well however, I need to add a feature that will check
whether the code in DHL.DHL ran successfully.

Private Sub Cmd1_Click()
Dim XL As Object
If XL Is Nothing Then
Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open strUser & "\Application
Data\Microsoft\Excel\XLSTART\PERSONAL.XLS"

End If
XL.Run "PERSONAL.XLS!DHL.DHL"
MsgBox "File conversion Completed"
XL.Workbooks.Close
XL.Quit
End Sub

 

[ryguy7272]

It is quite easy to perform operations in Excel, and control the entire
process from Access.

Make sure you set a reference to Excel, and then run this code in an Access
module:


Option Compare Database
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet
Sub Rep()
Dim strFile As String
strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls"

‘Of course, this is just an example; put the actual path to your actual file
here…

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet

' Your Excel code begins here…you can record a macro and make the process
super easy!!

End With
' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing
End Sub

HTH,
Ryan---: