Trying to use VLOOKUP for files in Dropbox

[trietta]

First time using dropbox for this. I want to create VLOOKUPs referencing several different files, but the problem is that everything is in Dropbox and I need to download the files to work on them.

Is there any way to create the links without having to download all of the files onto my local drive?

 

[kpasa]

You could download the files to a temporary folder, import the data via vba, then delete the files in the temp folder.

Here is the code I use to download files from a URL:

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long
    'strSavePath includes filename
    'returns 0 if download successful
    DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
End Function

To my knowledge, you have to download the file for Excel to access it.

 

[trietta]

Thanks! But when someone else opens the file, since the formulas reference my local drive and their computer is going to reference their local drive, the VLOOKUPs won't work, correct?

 

[kpasa]

Thanks! But when someone else opens the file, since the formulas reference my local drive and their computer is going to reference their local drive, the VLOOKUPs won't work, correct?

Correct, at which point, you'd need to write vba code to perform your lookups and calculations. That way you can dynamically code the file paths of the supporting documents.