Verify File Location 2

[kashford]

I have a macro that reads an user supplied input file and then converts it to a formatted CSV file. My problem is that I can't find a command that will allow me to verify that the file name the users will enter into the macro is correct. I have tried several different variations of the Exists() command, but no success.

I was going to look in the macro editor help file, but I can not find it installed on any of the machines here.

Any help would be greatly appreciated.

 

[IknowMe]

Here's a sample from the EB help files on how to do something similar without an Exists() command. I assume your macro is in EB?

Sub main
   Dim filenumber
   Dim filename as String
   filenumber=FreeFile
   [COLOR=red]filename=InputBox("Enter a file to open: ")
   On Error Resume Next
   Open filename For Input As filenumber
   If Err<>0 then
      MsgBox "Error loading file.  Re-run program."
      Exit Sub
   End If[/color]
   MsgBox "File " & filename & " opened as number: " & filenumber
   Close #filenumber
   MsgBox "File now closed."
End Sub

 

[calculus]

The Dir$ function is the best method.

If UCase(Dir$(Environ("UserProfile") & "\desktop\Import POs", vbDirectory)) <> "IMPORT POS" Then...

This will pull back the directory or file name. If it doesn't exist it returns "". This is a good way to determine if a file already exists.

calculus

 

[IknowMe]

Good call with Dir Star for Calc

Could also..

If Dir("YourDrive\YourFolder\YourFile.ext") <> "" Then ...

 

[Skie]

Type OPENFILENAME
     lStructSize As Long
     hwndOwner As Long
     hInstance As Long
     lpstrFilter As Long
     lpstrCustomFilter As Long
     nMaxCustFilter As Long
     nFilterIndex As Long
     lpstrFile As Long
     nMaxFile As Long
     lpstrFileTitle As Long
     nMaxFileTitle As Long
     lpstrInitialDir As Long
     lpstrTitle As Long
     Flags As Long
     nFileOffset As Integer
     nFileExtension As Integer
     lpstrDefExt As Long
     lCustData As Long
     lpfnHook As Long
     lpTemplateName As Long

End Type

Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long
Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
Declare Function GetModuleHandle Lib "kernel32" Alias "GetModuleHandleA" (ByVal lpModuleName As String) As Long

Dim pOpenFileName As OPENFILENAME


Global Const OFN_READONLY = &H1
Global Const OFN_OVERWRITEPROMPT = &H2
Global Const OFN_HIDEREADONLY = &H4
Global Const OFN_NOCHANGEDIR = &H8
Global Const OFN_SHOWHELP = &H10
Global Const OFN_ENABLEHOOK = &H20
Global Const OFN_ENABLETEMPLATE = &H40
Global Const OFN_ENABLETEMPLATEHANDLE = &H80
Global Const OFN_NOVALIDATE = &H100
Global Const OFN_ALLOWMULTISELECT = &H200
Global Const OFN_EXTENSIONDIFFERENT = &H400
Global Const OFN_PATHMUSTEXIST = &H800
Global Const OFN_FILEMUSTEXIST = &H1000
Global Const OFN_CREATEPROMPT = &H2000
Global Const OFN_SHAREAWARE = &H4000
Global Const OFN_NOREADONLYRETURN = &H8000
Global Const OFN_NOTESTFILECREATE = &H10000
Global Const OFN_SHAREFALLTHROUGH = 2
Global Const OFN_SHARENOWARN = 1
Global Const OFN_SHAREWARN = 0

'-------------------------------------------------------
'
' Open File Dialog
'
'-------------------------------------------------------
'
Function GetFileName(Title$, FileType$) As String
  Dim Message$, Filter$, FileName$, FileTitle$, DefExt$
  Dim szCurDir$, APIResults&

    ' Define the filter string and allocate space in the "c" string
    Filter$ = "Old File (*." & FileType & ")" & Chr(0) & "*." & FileType & Chr(0)
    Filter$ = Filter$ & "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
    Filter$ = Filter$ & Chr(0)

    ' Allocate string space for the returned strings.
    FileName$ = Chr(0) & Space(255) & Chr(0)
    FileTitle$ = Space(255) & Chr(0)

    ' Give the dialog a caption title.
    Title$ = Title$ & Chr(0)

    ' If the user does not specify an extension, append MDB.
    DefExt$ = "txt" & Chr(0)

    ' Set up the defualt directory
    szCurDir$ = CurDir$ & Chr(0)

    ' Set up the data structure before you call the GetOpenFileName
    pOpenFileName.lStructSize = Len(pOpenFileName)
    pOpenFileName.hwndOwner = GetModuleHandle("Extra.exe")
    pOpenFileName.lpstrFilter = lstrcpy(Filter$, Filter$)
    pOpenFileName.nFilterIndex = 1
    pOpenFileName.lpstrFile = lstrcpy(FileName$, FileName$)
    pOpenFileName.nMaxFile = Len(FileName$)
    pOpenFileName.lpstrFileTitle = lstrcpy(FileTitle$, FileTitle$)
    pOpenFileName.nMaxFileTitle = Len(FileTitle$)
    pOpenFileName.lpstrTitle = lstrcpy(Title$, Title$)
    pOpenFileName.Flags = OFN_HIDEREADONLY + OFN_FILEMUSTEXIST
    pOpenFileName.lpstrDefExt = lstrcpy(DefExt$, DefExt$)
    pOpenFileName.hInstance = 0
    pOpenFileName.lpstrCustomFilter = 0
    pOpenFileName.nMaxCustFilter = 0
    pOpenFileName.lpstrInitialDir = lstrcpy(szCurDir$, szCurDir$)
    pOpenFileName.nFileOffset = 0
    pOpenFileName.nFileExtension = 0
    pOpenFileName.lCustData = 0
    pOpenFileName.lpfnHook = 0
    pOpenFileName.lpTemplateName = 0

    ' This will pass the desired data structure to the Windows API,
    ' which will in turn use it to display the Open Dialog form.

    APIResults& = GetOpenFileName(pOpenFileName)

    If APIResults& <> 0 Then
        GetFileName = Trim(FileName$)
    Else
        'return null string if no file selected
        GetFileName = ""
    End If

End Function


Sub Main
    Answer = GetFileName("Text for File Open Dialog", "txt")
    MsgBox Answer
End Sub

Well, if you want 'em just to be able to select it from a windows open dialogbox, then this is what I use. That way, the user can just browse to the path. Mad props to JimGE for this. YOu can award him a star for it

http://www.tek-tips.com/viewthread.cfm?qid=810124