Anyway to use Access to detect whether an Excel file exists?

[OC2]

Hi,

Im designing a system that will update and create a report on a 4 month cycle using Excel.

What I would like to know is, is it possible that access before updating checks whether the excel file already exists, and same for the worksheets within excel.

This will be to prevent and alert for the following cases.

1. When user logs onto access system, the system will do a status check to see what needs updating, ie if the excel report does not exist access will report that you need to create it.

2. If a new user is created, access will recognise that the specific worksheet for that user within the excel file does not exist there fore alerts the user to create it.

3. Access will check whether all the columns are filled up and if so will alert the user that the 4 month cycle is up and therefore a need to create a new report.

any help will be much apprieciated thx

 

[Windshpere]

I can't remember were i found the following code. I have made some changes to get it to work the way I wanted. let me know if this works. This code checks to see if a file exist at a specific location.



'_____________________________start_____________________
'_____________________________use sub test1 ____________
' Declare call to kernel32.exe to use the openfile to check file
' existence ( Access DIR function can fail when using UNC for server/share names )
Declare Function OpenFile Lib "kernel32" (ByVal lpFileName As String, _
lpReOpenBuff As OFSTRUCT, _
ByVal wStyle As Long) As Long

' Declare constants for passing to openfile
Public Const OFS_MAXPATHNAME = 128
Public Const OF_EXIST = &H4000

' Declare object used in openfile function
Type OFSTRUCT
cBytes As Byte
fFixedDisk As Byte
nErrCode As Integer
Reserved1 As Integer
Reserved2 As Integer
szPathName(OFS_MAXPATHNAME) As Byte
End Type

Function CheckFileExists(ByVal IN_FILENAME As String) As Integer

Dim iresult As Integer
Dim strucFname As OFSTRUCT
Dim strSearchFile As String

strSearchFile = IN_FILENAME

iresult = OpenFile(strSearchFile, strucFname, OF_EXIST)

'The above line causes OpenFile to search for the
'file Test on the server network path.
'Passing the OF_EXIST parameter tells the OpenFile
'function to search for the file, the file will not be
'opened or modified in any way.

CheckFileExists = iresult

End Function
'___________________________________________________________________________


'________________________for test______________________
Sub test1()

Dim strPath As String: strPath = "c:\temp\test.txt"

If CheckFileExists(strPath) <> 1 Then
MsgBox &quot;File does not exist&quot;, vbCritical
Else
MsgBox &quot;File looks good&quot;
End If


End Sub
-________________________end code______________________

 

[TudorSmith]

You can try Dir(&quot;filepath&quot. Substitute &quot;Filepath&quot; with the file you're looking for i.e. &quot;c:\program files\excel\my file.xls&quot;

The function returns &quot; &quot; or NULL (can't remember without trying it out) if the file is not located otherwise it will return the value &quot;my file.xls&quot;

So your code might be:

If IsNull(Dir(&quot;filepath&quot) Or Dir(&quot;filepath&quot = &quot;&quot; Then
MsgBox &quot;Oops! Can't find that file...&quot;
End If


birklea