How to tell if worksheet exist via Access vba

[LonnieJohnson]

I have an MS Access module that writes to a workbook.

My question is how can I tell if a worksheet exist in an excel workbook via vba?



ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[rmikesmith]

One way is to attempt to set an object variable:

On Error Resume Next
Set objSheet = WorkbookObject.Worksheets("Sheet Name")
If Err.Number <> 0 Then
  'missing worksheet message here
  Exit Sub
End If
'On Error Goto ErrorHandler 'or whatever; restore whatever error handling you were using

WorkbookObject represents whatever you are currently using to reference the workbook the code writes to. If an error occurs, displays a message then exits. If no error, then you have an object variable ref to the worksheet you can use.

Regards,
Mike

 

[WinblowsME]

Option Compare Database

Sub Temp()
   Dim xl_file As String, sheet_name As String
   
   xl_file = "C:\Documents and Settings\WinblowsME\Desktop\Temp.xls"
   sheet_name = "Sheet1"
   
   Debug.Print Sheet_Exists(xl_file, sheet_name)
End Sub

Private Function Sheet_Exists(xl_file As String, sheet_name As String) As Boolean
   Dim xl_app As Object, xl_wb As Object
   
   Set xl_app = CreateObject("Excel.Application")
   Set xl_wb = xl_app.Workbooks.Open(xl_file, , False)

   On Error Resume Next
   Sheet_Exists = Len(xl_wb.Sheets(sheet_name).Name)
   
   xl_wb.Close
   
   Set xl_wb = Nothing
   Set xl_app = Nothing
End Function