ms excel automation problem

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

Hi,
I have this function that allows me to check if a sheet exits on a specific
workbook. It works fine but i have a problem closing ms excel
It still running on my task window. Thank you all.
Bre-x


Function WorksheetExist(TF As String) As Boolean
Dim objXL As Object
TheFile = TF
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = False
.Workbooks.Open TheFile
Dim ws, wsName
wsName = "Main"
For Each ws In .Worksheets
If UCase(ws.Name) = UCase(wsName) Then
WorksheetExist = True
.Workbooks.Close
.Quit
Exit Function
End If
Next
WorksheetExist = False
.Workbooks.Close
.Quit
End With
Set objSht = Nothing
Set objXL = Nothing
End Function
 
Possibly there's something in the workbook that calculates automatically
on opening or does someting else that makes Excel think the workbook has
been changed. In that case Excel won't close the workbook or itself. Try
something like this instead:



Function WorksheetExist(WbkPath As String, _
SheetName As String) As Boolean

Dim objXL As Object
Dim ws As Object

WorksheetExist = False
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = False
.Workbooks.Open WbkPath
For Each ws In .Worksheets
If UCase(ws.Name) = UCase(SheetName) Then
WorksheetExist = True
GoTo CloseExcel
End If
Next
End With
CloseExcel:
With objXL
'close any/all workbooks without saving
Do While .Workbooks.Count > 0
.Workbooks(.Workbooks.Count).Close False
Loop
End With
objXL.Quit
Set objXL = Nothing
End Function



Hi,
I have this function that allows me to check if a sheet exits on a specific
workbook. It works fine but i have a problem closing ms excel
It still running on my task window. Thank you all.
Bre-x


Function WorksheetExist(TF As String) As Boolean
Dim objXL As Object
TheFile = TF
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = False
.Workbooks.Open TheFile
Dim ws, wsName
wsName = "Main"
For Each ws In .Worksheets
If UCase(ws.Name) = UCase(wsName) Then
WorksheetExist = True
.Workbooks.Close
.Quit
Exit Function
End If
Next
WorksheetExist = False
.Workbooks.Close
.Quit
End With
Set objSht = Nothing
Set objXL = Nothing
End Function
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Johh,
Thank you very much.
It works very well

Regards,

Bre-x


John Nurick said:
Possibly there's something in the workbook that calculates automatically
on opening or does someting else that makes Excel think the workbook has
been changed. In that case Excel won't close the workbook or itself. Try
something like this instead:



Function WorksheetExist(WbkPath As String, _
SheetName As String) As Boolean

Dim objXL As Object
Dim ws As Object

WorksheetExist = False
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = False
.Workbooks.Open WbkPath
For Each ws In .Worksheets
If UCase(ws.Name) = UCase(SheetName) Then
WorksheetExist = True
GoTo CloseExcel
End If
Next
End With
CloseExcel:
With objXL
'close any/all workbooks without saving
Do While .Workbooks.Count > 0
.Workbooks(.Workbooks.Count).Close False
Loop
End With
objXL.Quit
Set objXL = Nothing
End Function



Hi,
I have this function that allows me to check if a sheet exits on a specific
workbook. It works fine but i have a problem closing ms excel
It still running on my task window. Thank you all.
Bre-x


Function WorksheetExist(TF As String) As Boolean
Dim objXL As Object
TheFile = TF
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = False
.Workbooks.Open TheFile
Dim ws, wsName
wsName = "Main"
For Each ws In .Worksheets
If UCase(ws.Name) = UCase(wsName) Then
WorksheetExist = True
.Workbooks.Close
.Quit
Exit Function
End If
Next
WorksheetExist = False
.Workbooks.Close
.Quit
End With
Set objSht = Nothing
Set objXL = Nothing
End Function
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
Back
Top