closing excel

[kevcar40]

hi
i have the following module that opens excel copies the contents of the
sheet to a table, cleans the sheet and closes the workbook
all working fine up to now
I then quit XL this happens but after a few seconds i get a message
box telling me that my Xlsheet is now available for editing
how do i stop this ???


Public Sub WorkbookOpen()
Dim XL As Object, XLBook As Object, MyProjectPath As String
Dim XLsheet As Excel.Worksheet
DoCmd.SetWarnings False
MyProjectPath = "C:\Temp\Daily_info.xls"
If Dir(MyProjectPath) = "" Then GoTo Exit_Sub_WorkbookOpen
Set XL = CreateObject("Excel.Application")
Set XLBook = XL.Workbooks.Open("C:\Temp\Daily_info.xls")
XL.Worksheets(1).Activate
XL.Visible = True
'COPYING THE DATA
DoCmd.RunMacro "running_Getting_data_withoutwarnings"
Set XLsheet = XL.Worksheets("Daily_info")
'CLEARING THE CONTENTS
XLsheet.Range("A2:G500").ClearContents
XLBook.Close SaveChanges:=True
XL.Quit
DoCmd.SetWarnings True
Exit_Sub_WorkbookOpen:
Set XLsheet = Nothing
Set XLBook = Nothing
Set XL = Nothing
End Sub


many thanks


kevin

 

[Jim Cone]

Kevin,
I would change it so all of the objects are set to nothing
before quitting Excel (except for the XL object).
Also, Worksheets(1) doesn't appear to be used, so that
was eliminated.
(untested)...
Jim Cone
San Francisco, USA

'---------------------
Public Sub WorkbookOpen()
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLsheet As Excel.Worksheet
Dim MyProjectPath As String

DoCmd.SetWarnings False
MyProjectPath = "C:\Temp\Daily_info.xls"
If Dir(MyProjectPath) = "" Then GoTo Exit_Sub_WorkbookOpen
Set XL = CreateObject("Excel.Application")
Set XLBook = XL.Workbooks.Open("C:\Temp\Daily_info.xls")
Set XLsheet = XLBook.Worksheets("Daily_info")
XL.Visible = True

'COPYING THE DATA
DoCmd.RunMacro "running_Getting_data_withoutwarnings"

'CLEARING THE CONTENTS
XLsheet.Range("A2:G500").ClearContents
XLBook.Close SaveChanges:=True
Exit_Sub_WorkbookOpen:
Set XLsheet = Nothing
Set XLBook = Nothing
XL.Quit
Set XL = Nothing
DoCmd.SetWarnings True
End Sub
'-----------
hi
i have the following module that opens excel copies the contents of the
sheet to a table, cleans the sheet and closes the workbook
all working fine up to now
I then quit XL this happens but after a few seconds i get a message
box telling me that my Xlsheet is now available for editing
how do i stop this ???


Public Sub WorkbookOpen()
Dim XL As Object, XLBook As Object, MyProjectPath As String
Dim XLsheet As Excel.Worksheet
DoCmd.SetWarnings False
MyProjectPath = "C:\Temp\Daily_info.xls"
If Dir(MyProjectPath) = "" Then GoTo Exit_Sub_WorkbookOpen
Set XL = CreateObject("Excel.Application")
Set XLBook = XL.Workbooks.Open("C:\Temp\Daily_info.xls")
XL.Worksheets(1).Activate
XL.Visible = True
'COPYING THE DATA
DoCmd.RunMacro "running_Getting_data_withoutwarnings"
Set XLsheet = XL.Worksheets("Daily_info")
'CLEARING THE CONTENTS
XLsheet.Range("A2:G500").ClearContents
XLBook.Close SaveChanges:=True
XL.Quit
DoCmd.SetWarnings True
Exit_Sub_WorkbookOpen:
Set XLsheet = Nothing
Set XLBook = Nothing
Set XL = Nothing
End Sub


many thanks


kevin