Problem referring to OneDrive folder/files via VBA?

Hello - I have been working with the code below to use the .CSV filenames captured on a Worksheet ("RptData_Cleaned") as a 'key' to guide me to a set of .CSV files in a different folder (all in a Microsoft OneDrive environment) to then extract values from the matching file to append to the appropriate row in the "RptData_Cleaned" Worksheet. If I run this code from my 'C:\' drive, it works, if I do it from my OneDrive environment, I only get '#REF!' errors instead of the values I'm targeting. I've tried the Environ ("UserProfile") variable followed by the remainder of the folder path, but that did not help. Any suggestions on how to work around this apparent OneDrive issue?

Thank you!

Please Login or Register  to view this content.

Hi Patdools,
I did my tests with OneDrive directory and with a non OneDrive directory and I got not differences, your code works well
But, I was able to get a REF error when:
The name in the cell includes a wild-char for instance File123.xls* or File123.xls_
Then the instruction Dir(strPath & cell.Value) work fine because DIR() expects wild-chars and can handle them but
The statement "='" & strPath & "[" & cell.Value & "]" & strSheet & "'!A14" does not handle wild-chars and you get a REF error
So I would suggest you to look for wild-chars in the cell containing the file names
Or debug before assigning .formula I used a dumb variable a3 to debug the REF error to see if the formula is well written

Please Login or Register  to view this content.

Cheers
Sergio

Hi Sergio - thank you for the tip on the dummy variable. I did use that code and I see that '.Formula' holds the entire path of the file, the specific (and correct) filename, the Worksheet Name is captured, and the Cell reference ('A14') is also included (but not the actual Value contained in that cell). It all looks correct, yet I'm still ending up with the '#REF' error when running the code. What else can I look at to find out why I can't get the Values I'm targeting in 'A14' & 'B14'?

I guess you will have to debug: debug before assigning .formula I used a dumb variable a3 to debug the REF error to see if the formula is well written
I added to you code a line to debug the formula before it is assigned to the cell

Please Login or Register  to view this content.

Add a break point (F9) on line A3=...
And see what you get as a formula, copy and assign the value to a cell to see whats happens
Cheers
Sergio