Opening the first (and only the first) file present in a folder with VBA

[juancarlos1]

Hello!

There is the need to open the first and only the first Excel file present in a specific folder.
I am having some trouble using the Dir function with a relative reference.
This code is working when the full path is there placed, but the reference must be relative:

Dim FileName As String
FileName = VBA.FileSystem.Dir(ThisWorkbook.Path & "\Export files to process\*.xlsx")
If FileName = VBA.Constants.vbNullString Then
MsgBox "No file found in the folder"
Else
Workbooks.Open ThisWorkbook.Path & "\Export files to process\" & FileName
End If

Bad file name or number is the error returned.
Can anyone advise here?

Regards!

 

[John_w]

Relative to which folder? Describe exactly how the folder containing the required file is relative to the 'start' folder.

As written, ThisWorkbook.Path & "\Export files to process\, the "Export files to process" folder is a subfolder of the ThisWorkbook.Path folder.

 

[juancarlos1]

Relative to which folder? Describe exactly how the folder containing the required file is relative to the 'start' folder.

As written, ThisWorkbook.Path & "\Export files to process\, the "Export files to process" folder is a subfolder of the ThisWorkbook.Path folder.

Hi @John_w , The Macro-enabled tool is stored in a folder on my desktop. Inside this folder, aside from this file, there is another folder, where the export (.xlsx) files to be worked on are stored.
The desktop is connected to SharePoint, and this is the problem. Meanwhile, I have found that my code works if the tool is saved on the computer's disk.

ThisWorkbook.Path if the tool is in Sharepoint: https://mydrive......
ThisWorkbook.Path if the tool is in the computer's disk: C:\Users\......

Do you happen to know if there is a way of making the code work in Sharepoint folders?
Regards!

 

[John_w]

No, I don't know about Sharepoint folders.

 

[Zyndstoff]

Open the sharepointfolder with the Windows explorer, right click and select properties of folder. You will see the correct path there.
Should be something like this: \\plgabw-portal.itsysbw.cir@SSL\DavWWWRoot\persart\PR_intern.

 

[juancarlos1]

No, I don't know about Sharepoint folders.

Many thanks anyway

 

[juancarlos1]

Open the sharepointfolder with the Windows explorer, right click and select properties of folder. You will see the correct path there.
Should be something like this: \\plgabw-portal.itsysbw.cir@SSL\DavWWWRoot\persart\PR_intern.

Thank you for the tip!

 

[Zyndstoff]

Did it work out then?