Application.ThisWorkbook.Path not working with CreateObject("Scripting.FileSystemObject").FileExists

[shlobodon]

Hi Excelors!

I have the following code. If CreateObject("Scripting.FileSystemObject").FileExists(thesentence) Then has a result of FAILED. However, when it runs the line Workbooks.Open Filename:=currentdirectory & "\Rebate Report Output\" & strfile, the workbook opens. Obviously the file exists if it is being opened using the SAME PATH

Any advice?

Option Compare Text
Sub test8()
Dim currentdirectory As String
Dim strfile As String
Dim thesentence As String

customername = "BMS"
currentdirectory = Application.ThisWorkbook.Path
DateRange = "Oct'22 - Sep'23"
strfile = customername & "_Rebate Report_" & DateRange & ".xlsx"

thesentence = currentdirectory & "\Rebate Report Output\" & strfile

If CreateObject("Scripting.FileSystemObject").FileExists(thesentence) Then
MsgBox "worked"
Else
MsgBox "failed"
End If
Workbooks.Open Filename:=currentdirectory & "\Rebate Report Output\" & strfile

End Sub

 

[starl]

Technically I don't see anything wrong with your code - I copied it and used it both locally and on a network.
But - where is your workbook stored? I have had a huge problem lately with using "Thisworkbook.path" because unbeknownst to users, their desktop (for example) is in the cloud, so thisworkbook.path returns an https. Course, I'm not sure why your .Open would work - but then I haven't tried that specific command.

 

[shlobodon]

Technically I don't see anything wrong with your code - I copied it and used it both locally and on a network.
But - where is your workbook stored? I have had a huge problem lately with using "Thisworkbook.path" because unbeknownst to users, their desktop (for example) is in the cloud, so thisworkbook.path returns an https. Course, I'm not sure why your .Open would work - but then I haven't tried that specific command.

Thank you Starl! Yes, it is being pulled from a cloud. Thank you for your insight! I will have to find a way around it

 

[shlobodon]

Thank you Starl! Yes, it is being pulled from a cloud. Thank you for your insight! I will have to find a way around it

I think I will open the workbook since it does open, then test to see if it's open. Hopefully this would suit my needs