Copy worksheet to file two directories up

Question

I am working with a macro to copy data between worksheets in multiple folders. For this example the file is

\network_drive\Locations\site\network_scans\year\Month\results.xlsx

I am trying to copy a table from this file to another workbook two directories up. In this case

\network_drive\Locations\site\network_scans\Template.xlsx

I do this with an absolute path:

Sheets("Chart Data").Select
Selection.Copy
Workbooks.Open Filename:= _
    "\\network_drive\Locations\site\network_scans\Template.xlsx"
Range("A20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

I need this for multiple locations and sites (i.e. site 1, site 2, site 3, etc.). I am trying to make the path relative. If I were doing this manually I would use cd ../../ and then open Template.xlsx.

How do I reference relative paths in VBA?

EDIT
I think I got the syntax but now it generating

Run-time error '1004'
Sorry, we couldn't find C:\Users\USER\AppData\Roaming\Excel\XLSTART Scan Reports \filename.xlsx. Is it possible it was moved, renamed, or deleted?

Sheets("Chart Data").Select
Selection.Copy
Workbooks.Open CStr(ThisWorkbook.Path) & "\..\..\..\network_scans\Template.xlsx"
Range("A20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub

SNIP
It is addressing the file as if it were on the C drive instead of the network drive. Any thoughts on how to get it to see this as the network path instead of the local path?

[fso](https://stackoverflow.com/questions/39637185/go-up-one-folder-level#39639633) will help. You may have to run this twice to move up two levels. — Scott Holtzman, Sep 28 '18 at 15:40

score 1 · Answer 1 · answered Sep 28 '18 at 15:43

Copy the below code in a Module

Function GetGrandParentFolder() As String
    Dim oFSO As New FileSystemObject
    With oFSO
        GetGrandParentFolder = .GetParentFolderName(.GetParentFolderName(ThisWorkbook.Path))
    End With
End Function

Now you can call this UDF to get your grand folder name

NOTE: You have to add reference to Microsoft Scripting Runtime for the above UDF to work

score 0 · Answer 2 · answered Sep 28 '18 at 16:02

0

You can get the name of the filepath of the current workbook with

thisworkbook.path

From there, you can cast is as a string and the append the navigations you mentioned above:

cstr(thisworkbook.Path) & "\..\.."

to get you up two directories.

Good luck!

answered Sep 28 '18 at 16:02

oxwilder

756
5
14

Thank you very much but you forgot that part where I can't spell VBA, LOL. I understand what you are saying but am unable figure out how to integrate it into the above example. I have tried several iterations and no luck. – Jose Santiago Sep 28 '18 at 20:48
please see my edit to the original question. Thanks! – Jose Santiago Oct 01 '18 at 21:04
@JoseSantiago Ahh, I see what you're saying. I think somehow I missed that this was a network path. I believe that network files are opened in a temporary folder, which is why you got that error message above. You may need to map your network drive in order for the script to work right. But I believe Zac's solution really is the best. Add the proper reference (Tools>References>Microsoft Runtime Scripting). – oxwilder Oct 02 '18 at 13:09
I actually tried to use that solution first but with the information I was given and my level of knowledge I have been unable to figure out how to apply it after creating the module with the function above. My understanding of VBA is just above barely functional, without seeing it in context I tend to flail about wildly trying to make it work... :( – Jose Santiago Oct 02 '18 at 16:12

Copy worksheet to file two directories up

2 Answers2