×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How do I use FileExists?

How do I use FileExists?

How do I use FileExists?

(OP)
thread766-141175: Checking if a file exists.

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?

CODE

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

RE: How do I use FileExists?

That the quotes away from "txtWellID"

In quotes = literal string

No quotes = variable

RE: How do I use FileExists?

(OP)
Thanks MintJulep.  It is giving an error on the line

CODE

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?

RE: How do I use FileExists?

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

Quote:

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: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

RE: How do I use FileExists?

(OP)
Thanks, it looks like it is working now.  I added

CODE

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.

RE: How do I use FileExists?

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

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources