VBA to save to OneDrive

[Shales]

Hi,

I have a Macro spreadsheet which I am trying to share with other users via OneDrive.

Sharing the Spreadsheet is no problem but the other users cannot use my macro to export to OneDrive because they do not have access to the "my" username file path;

Sheets("Export").Select
    Sheets("Export").Copy
     Sheets("Export").Range("T2").Value = counter
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\ME\OneDrive - COMPANY\test\" & Format(Now(), "USER" & "ddmmyy") & "Batch" & counter & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False[\CODE]

Is there a way around this? I am open to potential workarounds as long as it's VBA. I can't simply create a dropbox solution, or anything like that.

Thank you,
Gav

 

[daverunt]

Sorry for the path error. I didn't do a file save otherwise I'd have noticed
You sorted it though and that's a better outcome.

 

[FuNeS13]

If you have a folder sync to your computer and you share that folder to other users, if you change anything in your file, they will see the updated file, goes the same the other way around, if they modify anything on any of your shared files or folders you will see them updated.

I have for example a folder from a co-worker synced to my computer... the path goes like this:

C:\Users\MyUser\MyCompany\Co-workers-Name - Folder name

So if I edit any file with vba,

 

[TreeDude]

I know this is an ANCIENT thread ... but I was beating my head on this one and stumbled across a solution that worked for me:
the answer is to use CurDir to get the "local" OneDrive path, rather than ActiveWorkbook.Path or ActiveWorkbook.FullName
CurDir is an Office VBA function, rather than Excel VBA but it works.
Info on CurDir and related functions: CurDir function (Visual Basic for Applications)

 

[gruntingmonkey]

I know this is an ANCIENT thread ... but I was beating my head on this one and stumbled across a solution that worked for me:
the answer is to use CurDir to get the "local" OneDrive path, rather than ActiveWorkbook.Path or ActiveWorkbook.FullName
CurDir is an Office VBA function, rather than Excel VBA but it works.
Info on CurDir and related functions: CurDir function (Visual Basic for Applications)

Ancient but saved my bacon!