Using DIR to determine if file exists

[CharlieT302]

Hi All,

I have a vba procedure that checks to see if a spreadsheet exists before creating it. I can use a DIR function to do this.

Currently:
If DIR("C:\Documents and Settings\My Documents\Temp_Data.xls") = "" then
Docmd.TransferSpreadsheet ...
else
MsgBox "Complete"

However, I want to check for the file in the MY Documents folder regardless of the computer or user logon.

I have tried:
If DIR("\\%UserProfile%\My Documents\Temp_Data.xls") then...


When I try the %UserProfile% approach, it creates the spreadsheet whether it exists or not. Obviously I have an error in code.

Any suggestions?

 

[Craig0201]

Credit to AllAPI.Mentalis.Org for this.

Place this in a module.

Const CSIDL_DESKTOP = &H0
Const CSIDL_PROGRAMS = &H2
Const CSIDL_CONTROLS = &H3
Const CSIDL_PRINTERS = &H4
Const CSIDL_PERSONAL = &H5
Const CSIDL_FAVORITES = &H6
Const CSIDL_STARTUP = &H7
Const CSIDL_RECENT = &H8
Const CSIDL_SENDTO = &H9
Const CSIDL_BITBUCKET = &HA
Const CSIDL_STARTMENU = &HB
Const CSIDL_DESKTOPDIRECTORY = &H10
Const CSIDL_DRIVES = &H11
Const CSIDL_NETWORK = &H12
Const CSIDL_NETHOOD = &H13
Const CSIDL_FONTS = &H14
Const CSIDL_TEMPLATES = &H15
Const MAX_PATH = 260
Private Type SHITEMID
    cb As Long
    abID As Byte
End Type
Private Type ITEMIDLIST
    mkid As SHITEMID
End Type
Private Declare Function ShellAbout Lib "shell32.dll" Alias "ShellAboutA" (ByVal hWnd As Long, ByVal szApp As String, ByVal szOtherStuff As String, ByVal hIcon As Long) As Long
Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" (ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Private Function GetSpecialfolder(CSIDL As Long) As String
    Dim r As Long
    Dim IDL As ITEMIDLIST
    'Get the special folder
    r = SHGetSpecialFolderLocation(100, CSIDL, IDL)
    If r = NOERROR Then
        'Create a buffer
        Path$ = Space$(512)
        'Get the path from the IDList
        r = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal Path$)
        'Remove the unnecessary chr$(0)'s
        GetSpecialfolder = Left$(Path, InStr(Path, Chr$(0)) - 1)
        Exit Function
    End If
    GetSpecialfolder = ""
End Function

My Docs should be GetSpecialfolder(CSIDL_PERSONAL)

C

 

[Remou]

You could also try:

[tt]CreateObject("WScript.Shell").SpecialFolders(SpName)[/tt]

Where SpName is, amongst others:
MyDocuments
Desktop

It works in Vista.

 

[CharlieT302]

Remou,

Thanks for the feedback, but I am not following. I need to check for the existence of a spreadsheet in the My Documents folder. If it doesn't exist, I want to run a query.

How does you statement check for the file?

 

[Remou]

It doesn't. You said, "However, I want to check for the file in the MY Documents folder regardless of the computer or user logon.". The line gets the My Documents folder for the current users. You can the check the folder easily enough using Dir.

 

[CharlieT302]

Remou,

I see how the command looks for the folder, but am unclear how to construct a statement that checks for the existence of a specific file within it.

Could you be more clear?

Thanks

 

[Remou]

You already have code for Dir.

strPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

If Dir(strPath & "\Temp_Data.xls") = "" Then
Docmd.TransferSpreadsheet ...
Else
MsgBox "Complete"
End If

 

[CharlieT302]

No luck,

I am getting the same results as with my original method. The If statement is being evaluated as true even when the spreadsheet file already exists.

 

[Remou]

Perhaps you could explain a little more? The code above will check the My Documents folder for a file, and if it is found, it will import it. What did you wish to do?

 

[CharlieT302]

I have it working. I was referencing "My Documents" rather than "MyDocuments" I wasn't aware of the need to eliminate the space.

Thanks for the help and patience.