Using variables with DIR Function

[jagrenet]

I am using a couple of variables with a DIR function and, while the first variable works just fine, the second doesn't.

Dim Path As String
Dim PSAP_Name As String
Dim AppAvail As String

PSAP_Name = Worksheets("Sheet1").Range("C5") - (Cell C5 contains the word WALTON)
Path = "C:\Monthly_Reports\" & PSAP_Name & "\"
AppAvail = Dir(Path & "Application Availability*.docx")

OpenFile = Path & AppAvail

Set wd = New Word.Application
wd.Visible = True
wd.DisplayAlerts = wdAlertsNone

Set doc = wd.Documents.Open(OpenFile, ReadOnly)

As I step through the code I can see that "Path" actually reads "C:\Monthly_Reports\WALTON\", like it is supposed to.
When I look at the variable "AppAvail" it only shows "C:\Monthly_Reports\WALTON\" but does not include the filename specified - "Appllication Availablity*.docx".

I'm not exactly sure what I am missing here.
Thank you in advance,

Jeff

 

[DanteAmor]

Try this, and comment the result.

Sub openword()
  Dim sPath As String, OpenFile As String
  Dim PSAP_Name As String
  Dim AppAvail As String
  Dim wd As Word.Application
  Dim Doc As Word.Document
 
  PSAP_Name = Worksheets("Sheet1").Range("C5").Value  '- (Cell C5 contains the word WALTON)
  sPath = "C:\Monthly_Reports\" & PSAP_Name & "\"
 
  AppAvail = Dir(sPath & "Application Availability*.docx")
 
  If AppAvail <> "" Then
    OpenFile = sPath & AppAvail
   
    Set wd = New Word.Application
    wd.Visible = True
    wd.DisplayAlerts = wdAlertsNone
    Set Doc = wd.Documents.Open(OpenFile, ReadOnly:=True)
  Else
    MsgBox "No files"
  End If
End Sub

 

[rlv01]

AppAvail = Dir(Path & "Application Availability*.docx")

DIR is a function used to determine the presence of a particular file or files. It returns a zero length string if nothing is found. So the reason variable "AppAvail" only shows "C:\Monthly_Reports\WALTON\" is because it cannot find any files matching the pattern "Application Availability*.docx" in folder C:\Monthly_Reports\WALTON\

 

[jagrenet]

AppAvail = Dir(Path & "Application Availability*.docx")

DIR is a function used to determine the presence of a particular file or files. It returns a zero length string if nothing is found. So the reason variable "AppAvail" only shows "C:\Monthly_Reports\WALTON\" is because it cannot find any files matching the pattern "Application Availability*.docx" in folder C:\Monthly_Reports\WALTON\

Thanks riv01 !! - It did not dawn on me at the time that DIR would either and only be, True or False. Armed with that "refresher", .... looking at @DanteAmor's code sample above makes perfect sense. Using the conditional statement to determine if the file is there --- or not --- Then set the AppAvail variable to the String, ... providing that the condition is True. This works perfectly. I thank you BOTH riv01 AND DanteAmor for helping me to clarify what I was missing .... and to find was a little unclear.

 

[jagrenet]

Try this, and comment the result.

Sub openword()
  Dim sPath As String, OpenFile As String
  Dim PSAP_Name As String
  Dim AppAvail As String
  Dim wd As Word.Application
  Dim Doc As Word.Document
 
  PSAP_Name = Worksheets("Sheet1").Range("C5").Value  '- (Cell C5 contains the word WALTON)
  sPath = "C:\Monthly_Reports\" & PSAP_Name & "\"
 
  AppAvail = Dir(sPath & "Application Availability*.docx")
 
  If AppAvail <> "" Then
    OpenFile = sPath & AppAvail
  
    Set wd = New Word.Application
    wd.Visible = True
    wd.DisplayAlerts = wdAlertsNone
    Set Doc = wd.Documents.Open(OpenFile, ReadOnly:=True)
  Else
    MsgBox "No files"
  End If
End Sub

DanteAmor - Thank you !! - As you may notice in my response to riv01 ....... I had forgotten about the DIR function being only "0" or "1" - False or True. But, when I saw your suggestion utilizing the IF, Then Conditional statement, ... the light bulb went off in my head .... A serious "Ah-ha' moment for sure. I thank you both for getting me back on track !!!