How to set path for files in SharePoint and OneDrive

Occasional Contributor

Hi Excell community,

 

I had some files that were local on my computer. I have migrated these files into my OneDrive and SharePoint. These files contain Macro code that I use to update the other files. The file path in the Macro code still points to the local file path and I need to change this to the files in OneDrive.

 

This is the code that points to the local files (please note I don't have any experience in Macro code):

Sub COPIASTOFCONDN()
'
' COPIASTOFCONDN Macro
'

'
    Workbooks.Open Filename:= _
        "S:\Local_DB\020cocoa\DOWNLOADS\data\PRUEBASTOC.xlsx"
  
    Workbooks.Open Filename:= _
        "S:\Local_DB\020cocoa\DOWNLOADS\data\STOFCONDN.xlsx"
    Range("A1").Select
    Windows("PRUEBASTOC.xlsx").Activate
    Sheets("STATEMENTOFCONDITIONS").Select
    Columns("A:F").Select
    Selection.Copy
    Windows("STOFCONDN.xlsx").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:F").Select
    Range("F1").Activate
    Application.CutCopyMode = False
    Selection.Style = "Comma"
    ActiveWorkbook.Save
    ActiveWindow.Close
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

 

Can someone please help?

Any help will be much appreciated.
 

1 Reply
If you open "PRUEBASTOC.xlsx" manually you can go to the VBA editor window (alt+F11) and in the immediate pane (control+g), you type this line:
?ActiveWorkbook.FullName
and hit Enter. The VBA editor will then display the full path and name of the file you just opened, which you can subsequently copy and paste on top of the current path in your macro. Repeat this process for the other file(s) in your code.