dir function on OneDrive Excel file

Question

dir function on OneDrive Excel file

Heung-Jae Lee 1

VBA Procedure shown under is woks in correct manner when the Excel file saved on MyComputer forder, however, it doesn't work correctly when the same Excel file saved on OneDrive for file sharing.

When I open the Excel file from OneDrive, the 'dir function' (the code line 'DimExists = Dir(DimFullName)' shown under) looks not work correctly.

There is file of specified filename in the specified folder, but, Dir(DimFullName) returns NullString("") unlikely it returns text of 'DimFileName' when the same code was run from the Excel file from MyComputer forder.

What is my mistake and how do I solve his problem?
Please let me have your kind advice and help.

.......

DimPath = ThisWorkbook.Path 

DimFolder = DimPath & "\10 Folder\00 DataFolder" 

DimFileName = "ReqForm" & "(" & Format(DimDate, "yyyy-mm-dd") & "-" & Format(n, "0#") & ").pdf" 

DimFullName = DimFolder & "\" & DimFileName 

DimExists = Dir(DimFullName) 

If DimExists <> "" then .....

.....

Viorel 122.5K Reputation points

2021-11-29T08:43:25.597+00:00

Instead of DimPath = ThisWorkbook.Path, try using the explicit path, for example: DimPath = "C:\Users...\OneDrive\MyFiles". To find it, go to File tab, Info, "Open file location". But probably the path can point to any folder, not on OneDrive only. If Dir works with such paths, then maybe add a feature: let the user to specify this working folder using a configuration worksheet or form, made by you.
Heung-Jae Lee 1 Reputation point

2021-12-14T08:01:51.987+00:00

Dear Viorel-1,

Yes, when changing code, as you advised, it works well.
The sub checks files saved on MyComputer folder rather than OneDrive folder, and so dir returns filename.
And then save file as OtherName, and it synchronized to MyComputer folder.
So sub works proferly as I expected.

When I work on MyPC, I think it may work without problem, because the process is reverse of 'first work in MyComputer ans sync to OnDrive.

However, when I connect OneDrive through other PC and run the mentioned sub then I think it may not work proferly.
May cause similar problem because there is no same folder and/or filethe in the other PC.
So I need to sole this problem.

Anyhow your advice is very helpful and interesting.
Thank you very much.

1 answer

Your answer

Viorel 122.5K Reputation points

2021-11-29T08:43:25.597+00:00

Instead of DimPath = ThisWorkbook.Path, try using the explicit path, for example: DimPath = "C:\Users...\OneDrive\MyFiles". To find it, go to File tab, Info, "Open file location". But probably the path can point to any folder, not on OneDrive only. If Dir works with such paths, then maybe add a feature: let the user to specify this working folder using a configuration worksheet or form, made by you.
Heung-Jae Lee 1 Reputation point

2021-12-14T08:01:51.987+00:00

Dear Viorel-1,

Yes, when changing code, as you advised, it works well.
The sub checks files saved on MyComputer folder rather than OneDrive folder, and so dir returns filename.
And then save file as OtherName, and it synchronized to MyComputer folder.
So sub works proferly as I expected.

When I work on MyPC, I think it may work without problem, because the process is reverse of 'first work in MyComputer ans sync to OnDrive.

However, when I connect OneDrive through other PC and run the mentioned sub then I think it may not work proferly.
May cause similar problem because there is no same folder and/or filethe in the other PC.
So I need to sole this problem.

Anyhow your advice is very helpful and interesting.
Thank you very much.

Answer 1

Hi @Heung-Jae Lee ，

Welcome to Q&A forum~
But tag "office-excel-itpro" focuses on general issue of Excel client, as your issue is related to VBA code, to better help you, I would add the related tag "office-vba-dev". Thanks for your understanding.

I searched the similar threads, one is "Dir (myPathName & myExtension)" online, Dir function of VBA is not applicable to files on OneDrive Cloud such as files with "https:/ paths", but to files on local files.
(Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.)
Another one is "'Dir' to check existence of file in OneDrive", that try to use other fucntions, such as GetAbsolutePath Function in the FileSystemObject.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Heung-Jae Lee 1 Reputation point

2021-12-14T11:13:33.423+00:00

Thanks for your advice.

I understand that Dir function of VBA is not applicable to files on OneDrive Cloud.
I will try and test GetAbsolutePath Function in the FileSystemObject as your advice.

If I will have any result, will replay.

Many thanks.

Share via

dir function on OneDrive Excel file

1 answer

Your answer