dir function on OneDrive Excel file

Heung-Jae Lee 1 Reputation point
2021-11-26T09:04:30.367+00:00

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 .....

.....
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,618 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Emily Hua-MSFT 27,466 Reputation points
    2021-11-29T06:14:59.423+00:00

    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.