How do I use FileExists? 1

[bg3075]

thread766-141175

Working off of the thread above, I am trying to have VBA code

1. Open a Select Query, on a Hyperlink field, based on a list box selection ("txtWellID")
2. Check if a PDF file exists for the item in "txtWellID" then
a. Open the hyperlink, or else
b. Display a MsgBox and close the query

I am a bit of a Virgin VBA coder, and have never used the "FileExists" method, but believe it to be what I need to accomplish this task. Below is a rough sample of my attempt. It was working to open the hyperlink before adding "FileExists" method, so I know that portion of code was working; but I did change around to add "FileExists". Can anyone modify for me please?

Private Sub cmdOK_Click()
'Opens Groundwater Trend Charts, based on hyperlinks in the table "Well_info"
Dim qryTrendChartLinks As Hyperlink
Dim txtWellID As String

txtWellID = Forms!frmWellTrendChartsDialog!txtWellID

On Error GoTo ErrorHandler

Set FSO = New FileSystemOBject
DoCmd.OpenQuery ("qryTrendChartLinks")

If FSO.FileExists("D:\Trend Charts" & "txtWellID" & ".pdf") Then

    
DoCmd.RunCommand acCmdOpenHyperlink
DoCmd.Close acQuery, "qryTrendChartLinks", acSaveNo

Else

'If Not ("qryTrendChartLinks") Like "*" Then
    MsgBox ("Could not find a report")

ErrorHandler:             MsgBox ("Cannot find Trend Chart")
    DoCmd.Close acQuery, "qryTrendChartLinks", acSaveNo
   
    
    End If

End Sub

 

[MintJulep]

That the quotes away from "txtWellID"

In quotes = literal string

No quotes = variable

 

[bg3075]

Thanks MintJulep. It is giving an error on the line

Set FSO = New FileSystemOBject

stating "Compile Error: User Defined Type not defined". This is part of code I copied / pasted from the thread I referenced in my first post. Do I need to declare FSO as something possibly?

 

[IRstuff]

Well, you know, there is a help system in VBA

Type Property


Description

Returns information about the type of a file or folder. For example, for files ending in .TXT, "Text Document" is returned.

Syntax

object.Type

The object is always a File or Folder object.

Remarks

The following code illustrates the use of the Type property to return a folder type. In this example, try providing the path of the Recycle Bin or other unique folder to the procedure.

Sub ShowFileSize(filespec)
Dim fs, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(filespec)
s = UCase(f.Name) & " is a " & f.Type
MsgBox s, 0, "File Size Info"
End Sub

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[bg3075]

Thanks, it looks like it is working now. I added

Dim FSO
 Set FSO = CreateObject("Scripting.FileSystemObject")

to the declaration section of code, but also had to add a "/" to the end of the file path, being that I did not add a FindFolder or Dir method. But, instead of using a table to open the hyperlinks, now I'd rather find out how to open the PDF file (with the machine's default application). Thanks.

 

[bg3075]

Got it... was actually much simpler than I thought it would be. I was able to eliminate the query, and hyperlink table, all together.

Private Sub cmdOK_Click()

'Opens Groundwater Trend Charts, first checking if PDF exists
Dim strDocument As String
Dim txtWellID As String
 Dim FSO
 Set FSO = CreateObject("Scripting.FileSystemObject")

txtWellID = Forms!frmWellTrendChartsDialog!txtWellID
strDocument = ("D:\Charts\" & txtWellID & ".pdf")

If FSO.FileExists("D:\Charts\" & txtWellID & ".pdf") Then

Application.FollowHyperlink strDocument
    
Else

    MsgBox ("Could not find a chart for the specified well")
  
    End If

End Sub

Thanks very much to all of you for assisting.