Using filepaths in VBA code - files located on DropBox

John Steel

New Member
Joined
Feb 7, 2017
Messages
10
Hello and thanks for considering an issue I'm bringing up.

First off - this is relevant only to files stored on DropBox or, very likely, other cloud storage platforms. And only due to the files being accessed on different computers.

The problem appears to be that Excel typically uses absolute filepaths and, when accessing the file on different computers via DropBox, the local filepath varies based on the computer.

My code has the following line which works great from the "jstee" computer, but return error code 400 on other computers. At the moment I would also like for it to work for "ematt" computer, but, ideally, would like for it to adapt to other computers the file is accessed from.

Not sure it matters, but the line below is utilized in a macro that looks up a referenced .jpeg file from a folder on DropBox and places it in a certain place in the worksheet. The Comp_photos folder is within the base level DropBox folder and the file with the macro is located deeper in the DropBox directory. This file is generated from a template and must be able to access the Comp_photos folder from wherever it is in the DropBox directory.

I've been struggling with this for over a year now and would be elated for any help. Thank you!

Folderpath = "C:\Users\jstee\Dropbox\Comp_photos"
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
You can adapt your code using

Code:
Dim filepath as string 
filepath = environ("USERPROFILE")
Try:

Code:
Dim filepath as string

Filename:= Environ("USERPROFILE") & "\Dropbox\Comp_Photos"
 
Last edited:

John Steel

New Member
Joined
Feb 7, 2017
Messages
10
Anyone have any thoughts on this as a possible solution? I saw something like it elsewhere but I've not gotten it to work.

Folderpath = "C:\Users" & Environ("username") & "\Dropbox\Comp_photos"
 

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
661
cant edit above but should be

Code:
Dim Filepath As String

Filepath = Environ("USERPROFILE") & "\Dropbox\Comp_Photos\"
tested and working here
 

John Steel

New Member
Joined
Feb 7, 2017
Messages
10
Thx Jumbo

Should it be filepath or folderpath?
 

John Steel

New Member
Joined
Feb 7, 2017
Messages
10
Left it as folderpath and it seems to work fine. Gotta test it on a second computer later tonight.

Thanks Jumbo!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,901
Messages
5,411,116
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top