Saving files using Relative Filepath Location

[branston]

Hi

On my excel sheet (located c:\users\documents\branston\savefiles.xlsm) I have a macro which saves some pdf files located elsewhere in my user area. The Filepaths on the excel sheet (pointing to the pdfs) are absolute e.g. c:\files\Test1.pdf, c:\files\Test2.pdf, c:\files\Test3.pdf. No problems saving here.

However if I move my 'files' folder containing the pdfs to c:\users\documents\branston\files\ and try printing by making the filepaths relative ie. \Test1.pdf, \Test2.pdf, \Test3.pdf.... the files do not save.

Probably making an obvious error but I've tried various combinations (below) and none work.

Test1.pdf, Test2.pdf, Test3.pdf....
\Test1.pdf, \Test2.pdf, \Test3.pdf....
..\Test1.pdf, ..\Test2.pdf, ..\Test3.pdf....
\\Test1.pdf, \\Test2.pdf, \\Test3.pdf....

Can anyone help?

Thanks in advance.

[Paul_Hossler]

Q:

c:\users\documents\branston\savefiles.xlsm or c:\users\branston\documents\savefiles.xlsm

I'll assume the latter

I think you need to change your default folder

I'd use the third version below since it's a little more general

    ChDir "c:\users\branston\documents\files\"


    ChDir Environ("USERPROFILE") & "\Documents\files"

    ChDir CreateObject("Shell.Application").Namespace(CVar(5)).Self.Path & Application.PathSeparator & "files"


...save TEst1.pdf

[branston]

Hi Paul

Erm… I wasn't actually changing anything in the VBA code. Maybe I wasn't clear in my post but I've attached 2 images to show what I mean. The absoluteFilePath image saves the files fine. The second doesn't even though I've copied the topic files into the same folder as my excel sheet. I need it to be completely relative as I'm moving between computers/buildings/servers.

Some more info below

c1 is just the range in column D. p2 is the destination folder. Some snippet of codes.

  
p2 = "C:\users\branston\documents\files\"

  Set rng1 = Worksheets("Main").Range("D5:D" & LastRow)

For Each c1 In rng1
…
…
FileCopy c1, p2
          count = count + 1
...

Like I said I have no problems with the absolute filepath and apologies if you've already answered my question in your earlier post(!)

[Paul_Hossler]

Well if

p2 = "C:\users\branston\documents\files"

and

Cell D1 = "\files\TOPICS_01.pdf"

it would seem that your For Each c1 in rng1 loop would be trying to

FileCopy "\files\TOPICS_01.pdf", "C:\users\branston\documents\files"


I believe that FileCopy wants the full file name


So MAYBE you want something like

FileCopy Thisworkbook.Path & c1, "C:\users\branston\documents" & c1




Sorry, but I'm still not clear on the source and destination folders

[branston]

Thanks Paul.

If FileCopy wants the full path then that explains it.

But sorry p2 was meant to be whatever directory exists when I login to another business with my 'files folder'. So it could be c:\etc..etc.\files\ or z:\etc..etc.\files\ or any other drive.

I will try FileCopy Thisworkbook.Path & c1, "C:\users\branston\documents" & c1 but I think I really need FileCopy Thisworkbook.Path & c1, p2 & c1 as I could be on any network drive.

Apologies if my post is not clear but thank you for your help so far - much appreciated.

[Paul_Hossler]

Your post #3 has p2 = .... \files and the col D entry starts with \files\...

I don't think you need the \files in p2