Access-Excel Automation

  • Thread starter Thread starter Rafi
  • Start date Start date
R

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
 
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---:
 
Back
Top