retrun the value from a cell in another workbook that is in a URL path and the URL path is in a cell

[sdoppke]

Hi Everyone, sorry for the long title. I am trying to return a value that is in a closed workbook that is from a URL path. AND the URL path is in a cell

My Macro scrips is like so:

Sheets("Schedule Dashboard").Range("F4") =

after that needs to come the path, which is located in sheet2 A1. the sheet to look in on the closed workbook (from the path) is Week 1$AT$1.


Hope my explanation was clear enough. The reason I cannot just input the path is the path is created in cell A1 and is always different.

Thanks in advacne

sd

 

[bensonsearch]

try setting the path to a variable (which gets it from a1) then use that to call the value. from what i read its in another separte workbook yes? not another sheet?

 

[sdoppke]

try setting the path to a variable (which gets it from a1) then use that to call the value. from what i read its in another separte workbook yes? not another sheet?

Thanks a ton for the reply!! How would that look if i was trying to say get the value from Workbook(Path is in A1) Sheet "Week 1" Cell AT1 and place the value retried in cell A2 of the active workbook?

sd

 

[bensonsearch]

something like the below, put this in a module

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).Address(, , xlR1C1)
 
    MsgBox arg
 
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
 
End Function

then under your event (button,form activate which ever) put

dim dsa as variant
 
  p = thisworkbook.sheets("Sheet1").range("A1").value
'ensure this is full path with \ at the end
   f = "b.xlsm" 'name of file
   s = "Week 1" 'sheet of file
   a = "AT1" 'where you are looking
   dsa = GetValue(p, f, s, a)
thisworkbook.sheets("Week 1").range("A2").vaule = dsa

may need tweaking as I havent tested yet (sorry)

 

[sdoppke]

something like the below, put this in a module

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).Address(, , xlR1C1)
 
    MsgBox arg
 
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
 
End Function

then under your event (button,form activate which ever) put

dim dsa as variant
 
  p = thisworkbook.sheets("Sheet1").range("A1").value
'ensure this is full path with \ at the end
   f = "b.xlsm" 'name of file
   s = "Week 1" 'sheet of file
   a = "AT1" 'where you are looking
   dsa = GetValue(p, f, s, a)
thisworkbook.sheets("Week 1").range("A2").vaule = dsa

may need tweaking as I havent tested yet (sorry)

Thank you so much for helping. Ive ben struggling with this for a while now.

Im getting an error of "Runtime error 52 Bad Filename or Number"

Here is what I used. Maybe you can see an error?

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).Address(, , xlR1C1)
 
    MsgBox arg
 
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
 
End Function

Sub Schedule_Posted_YESNO()
Dim dsa As Variant
 
  p = ThisWorkbook.Sheets("Schedule Dashboard").Range("R34").Value
'ensure this is full path with \ at the end
   f = "AugustSchedule7886.xlsx" 'name of file
   s = "Week 1" 'sheet of file
   a = "AT1" 'where you are looking
   dsa = GetValue(p, f, s, a)
ThisWorkbook.Sheets("Schedule Dashboard").Range("F4").vaule = dsa
End Sub

Schedule Dashboard R34 has: (changed part of the url cause is for work)
http://infonet.abcdefg.com/sites/retail/WFM/West/Pacific Northwest/Seattle Metro/ (with out the "AugustSchedule7886.xlsx", should it have this?)

Thanks for hangin in there with me.

sd

 

[bensonsearch]

This code works with a file on the network or local computer eg: c:\test.xls

or any other folder set/drive letter

to work from web would be different, would have to download it first. I beleive this can be done with code.

something like this thread would work good

http://www.mrexcel.com/forum/showthread.php?t=352868