I have a bunch of Excel workbooks that are stored on a shared online directory (kind of like Dropbox) in different folders. I am creating a master workbook to pull values from these Excel workbooks into a summary. While testing I've managed to create one that works if the workbooks are stored in my disk drives and I refer to their directories, but not via the HTTP links they are stored on.
I'm currently using the ExecuteExcel4Macro macro to retrieve values from closed workbooks on my computer and passing the directory as the argument. Some code for example which works if it's a directory on the hard drive:
Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "Budget.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub
Is there any way to get this to work with direct HTTP links to Excel workbooks instead of directories? Downloading them all into my computer isn't really an option, unless I could somehow change it to download each workbook automatically, get the value and then delete it, or something like that?
