VBA save document to One Drive Excel file.

[ashani]

Hi everyone,

I wonder if someone could please guide me, I have a document which automatically saves certain columns to another Excel document before closing, however I would like that to save to my file in One Drive. I'm unable to do so - please could you help.

This is the VBA code I'm currently using :

Dim r As Long, lr As Long, Archieve As Workbook
Application.ScreenUpdating = False

r = 4

lr = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(r, 23), Cells(r, 28)).Copy

Set archive = Workbooks.Open("C:\Desktop\Test.xlsx")
Worksheets("Test").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
archive.Close SaveChanges:=True
Application.ScreenUpdating = True

 

[Z51]

In your Dim statement, you spelled it Archieve

In the code you're referencing archive

When saving to OneDrive, your desktop is typically a subfolder of that folder, not c:\Desktop\... either. If anything, a standard desktop folder would be C:\Users\Your_User_ID\Desktop

With OneDrive, it's probably more like "C:\Users\Your_User_ID\OneDrive\Desktop\"

 

[ashani]

Thank you for the reply.

It's still not working - I just want Workbook in One Drive to open, copy the cells value and close
thank you

 

[frabulator]

If you are wanting to use this on multiple profiles (IE, computers or accounts) you might want to use the Environ function to get the current user. Please see below, which returns the current users OneDrive from the default location:

Public Function GetOneDrivepath() As String
     GetOneDrivepath = Environ$("USERPROFILE") & "\OneDrive"
End Function

Sub TestLocation()
    MsgBox GetOneDrivepath
End Sub

Using your code as an starting point, and the one I provided, I have created a script that checks if the OneDrive file of "Desktop\Test.xlsx" Exist. If it does, then run the code, if it doesnt, it returns the value it was looking for. Use this to adjust the code as needed to open and save your doc.

Public Function GetOneDrivepath() As String
     GetOneDrivepath = Environ$("USERPROFILE") & "\OneDrive"
End Function



Sub Run()

    Dim r As Long, lr As Long, Archieve As Workbook
    Application.ScreenUpdating = False
    
    r = 4
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(r, 23), Cells(r, 28)).Copy
    
    Dim ODDoc As String
    ODDoc = GetOneDrivepath & "\Desktop\Test.xlsx"
    Dim strFileExist As String
    strFileExist = Dir(ODDoc)
    
    If strFileExis = "" Then
        MsgBox ("file of " & ODDoc & " does not exist")
    Else
        Set Archieve = Workbooks.Open(ODDoc)
        Worksheets("Test").Select
        Range("A1048576").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        archive.Close SaveChanges:=True
        Application.ScreenUpdating = True

    End If

It would also be best NOT to hide the screenupdating until AFTER you are sure it works. If you have already ran this code and it errored out after opening the document in question, then Excel would not warn you it was already opened because it would be hidden. Because it is already opened, then if you opened it again it would not allow you to change any values in it because it is read only mode.

To check this go into your Task Manager and see how many instances of Excel are currently running. Those are the books you have opened without saving.

 

[ashani]

Thank you so much @frabulator for your help.

Quick question - I don't want to copy on the file on my desktop. I just want "when multiple users click the button, certain data copies straight over to the shared workbook in my OneDrive workbook".

Would this code work in that scenario?

thank you once again

 

[frabulator]

Thank you so much @frabulator for your help.

Quick question - I don't want to copy on the file on my desktop. I just want "when multiple users click the button, certain data copies straight over to the shared workbook in my OneDrive workbook".

Would this code work in that scenario?

thank you once again

it doesnt copy anything to the desktop. The way I read your example was that there is a folder called 'Desktop' that held the document inside the OneDrive folder. Just change the string as needed to 'GetOneDrivepath' and '\' file name. The GetOneDrivepath should return the root directory for the onedrive folder.

 

[ashani]

Thank you @frabulator
I don't have a OneDrive folder in my user folder in C Drive - I access One Drive through the web. Currently, when I use it I get the error message "C:User etc not exist
Is this something can be done?
Thank you

 

[frabulator]

Record a macro of you saving a document to your one drive. Post that and let’s see where your one drive is located at.