Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
closing excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
closing excel
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 '----------- wrote in message oups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing workbooks w/o closing Excel | Excel Discussion (Misc queries) | |||
Closing Excel | New Users to Excel | |||
Closing Excel | Excel Discussion (Misc queries) | |||
Closing VB triggers closing Excel | Excel Programming | |||
closing excel after closing a workbook | Excel Programming |