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]

Hi,

Is this what you are after?

"C:\Users" & Environ$("Username") & "\OneDrive "........

I'm assuming you all use the same OneDrive.

 

[Shales]

Thanks for the reply. Yes this is potential what I am after, however I don't think I applied it correctly because it didn't work.

Yes we are all using the same OneDrive and all have access.

Could you please enter you line into my code line above? sorry to ask but I am not that clued up on VBA. My user name is Gavin.Shales if that's needed. Obviously I'll amend the rest to put my company name in etc.

Thank you!

 

[daverunt]

Could you please enter you line into my code line above? sorry to ask but I am not that clued up on VBA.

You and me both.

See if this works.
You can use enumerators for file formats too.
https://www.mrexcel.com/forum/excel-questions/759381-vba-saveas-fileformat-variable.html


FileFormat = "xlOpenXMLWorkbook"
ActiveWorkbook.SaveAs Filename:="C:\Users" & Environ$("Username") & "\OneDrive - COMPANY\test" & Format(Now() & "USER" & "ddmmyy") & "Batch" & counter & ".xlsx", FileFormat:=FileFormat, CreateBackup:=False

I'm not sure but you may not need the .xlsx but that will be obvious if it is duplicated when you test save.

 

[Shales]

That's great. Thank you. I'll have test.

Regards
Gav

 

[Shales]

Hi,

I am re-opening this one because whilst the solution above does work, it just doesn't work in the way that I need to. Hopefully some can tweak this (fingers crossed).

So what happening is, the user is able to save the doc to the onedrive but it is not saving to the location where it ideally should go. The doc will save to the user's own drive location. I ideally need it to save where I created it, which would be to my file path.

Any ideas how this can save to the original location? This would be;

"C:\Users\Gavin.Shales\........

Thank you.

 

[daverunt]

Hi Gavin,

I'm not a user of a shared OneDrive but surely if you are all sharing the same drive then the doc is saved on the users PC OneDrive folder by the macro
e.g "C:\Users\Fred Bloggs\........ and uploaded to the shared OneDrive Documents folder in the cloud?

Your PC then synchronises the file to your OneDrive documents folder "C:\Users\Gavin.Shales\........?

Or is that not how it works?

 

[Shales]

Hi,

I suppose the problem is that I don't know if it's even possible to do what i am wanting this to do.

I just want multiple users to export their work to one location.

I might have to explore a method of uploading directly using a Https link. That might be plausible.

 

[daverunt]

You'd need to explain in more detail as I am not entirely following.

- How do you expect users on another PC to save a file to your file path on your PC?
- Why is OneDrive not suitable?

 

[Shales]

OneDrive is suitable, I just “couldn’t” get it to work.

I have sorted it now though. In your code examples above, the \ was missing after USERS in the file path.

I should have spotted that. I’m not that much of an excel dummy.

Everything is working great. My users can all save in the one central location.

Thank you.