Check the existance of a Spread sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to check the existance of an Excel spreadsheet (where the name of the
file and the name of the worksheet are read from an Access form) and if it
does exist, I want to open it. If it doesn't, I want to open a template excel
spreadsheet and save it under the searched name.
Can anyone suggest a method to do this?

Thanks in advance
Toyfixer
 
Hullo,
you can do this

dim fs

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(path and name of your xl file)Then
'the file exist in the specified path and you can open it
else
'the file doesn't exist in the specified path. Open the template
End If

I assume you know how to open an XL file. If you don't know ask again.

HTH Paolo
 
Hi Paolo,
Thanks for the immediate reply. I know how to open the file. Infact I used
the following code to open the file.

Dim strWorkbookName As String ' Read from a combo box
Dim xlFrontEnd As Object
Dim objActiveWkbk As Object
Dim objXL as Object
Dim strPath as String

If Len(Dir(strWorkbookName)) > 0 Then
Set xlFrontEnd = objXL.Workbooks.Open(strWorkbookName)
Set objActiveWkbk = objXL.Application.ActiveWorkbook
Else
Set xlFrontEnd = objXL.Workbooks.Open(strPath & "FrontEnd.xls")
End If

And this works perfectly. If the file does not exist it opens up the
template (FrontEnd.xls). The only thing is that I don't know how to check the
existance of a worksheet in the workbook. (worksheet name is also read by a
combo box)
 
I think you can try something like that:

'*****you get the number of worksheets in your workbook
num_of_wrksht=objXL.Application.ActiveWorkbook.Worksheets.Count

for i=1 to num_of_wrksht
'****you check if the name of the worksheet exist in your collection
looping through all the worksheets name in your workbook
if objXL.Application.ActiveWorkbook.Worksheets(i).Name= strWorkbookName
exit for '***you got it
end if
next i
 
Back
Top