Use a wildcard in VBA to allow users on a shared google drive to auto link into Excel files

[Sarkman22]

Upfront, I apologize for my rookie skills in VBA- I am a point and click Macro creator so VBA is new to me. However, I feel I am trying to do something simple and could use some help....

I am trying to create a Macro in Excel (and Access later), to automatically link into an Excel file that is on a shared Google drive. However, when my co worker tries to run the macro, the file path changes because he is on his computer. How do I write the file path to use a * or wildcard to still find the file if the root folder changes?

E.g.

I write the macro referencing a file:
C:\Users\JSmith\Google Drive\Tables\SubTables\Table1.xlsx

My coworker tries to run the macro but it breaks because his file path is:
C:\Users\AWard\Google Drive\Tables\SubTables\Table1.xlsx

I tried:
C:\Users\*\Google Drive\Tables\SubTables\Table1.xlsx

But it didn't work.

Thank you very much for any help.

Andy

 

[ParamRay]

Upfront, I apologize for my rookie skills in VBA- I am a point and click Macro creator so VBA is new to me. However, I feel I am trying to do something simple and could use some help....

I am trying to create a Macro in Excel (and Access later), to automatically link into an Excel file that is on a shared Google drive. However, when my co worker tries to run the macro, the file path changes because he is on his computer. How do I write the file path to use a * or wildcard to still find the file if the root folder changes?

E.g.

I write the macro referencing a file:
C:\Users\JSmith\Google Drive\Tables\SubTables\Table1.xlsx

My coworker tries to run the macro but it breaks because his file path is:
C:\Users\AWard\Google Drive\Tables\SubTables\Table1.xlsx

I tried:
C:\Users\*\Google Drive\Tables\SubTables\Table1.xlsx

But it didn't work.

Thank you very much for any help.

Andy

This should work (the Environ function returns the Windows username):

sPath = "C:\Users\" & Environ$("UserName") & "\Google Drive\Tables\SubTables\Table1.xlsx"

 

[Sarkman22]

This worked perfectly, and is a humongous help! Thank you very, very much.... I hope you don't mind a quick follow up question...

Can I do the same thing in Access? I want to have a "linked Excel file" (a linked table) referring to an Excel file on a shared Google drive where someone else can open the Access database and run queries/macros that I setup and have the link to the Excel table update for their user name.

 

[ParamRay]

.
.

You could try it. I don't have much experience with Access

 

[Sarkman22]

No problem. Thank you again very much for the help! Have a good day.