Pulling / Pushing Values from/to closed Excel files

[pennylam]

Hi all,

Well, 2 questions in a single thread in fact.

Pulling values
From the web I've found some scripts in form of subroutine which is capable for pulling values, listed in follows:

Sub Try_run()
extension = ".xlsx"
Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension, Cells(3, 3), Cells(2, 2).Address(0, 0))
End Sub

Private Function GetValue2(path, file, sheet, range_ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue2 = "-"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(range_ref).Range("A1").Address(, , xlR1C1)
GetValue2 = ExecuteExcel4Macro(arg)
End Function

The above scripts works very well.
I try to make some changes but I can't get them correct for long...
1) the subroutine always pulls values from a fixed cell in the source worksheet (B2 in the above example)
how could this be change into a valuable in the running worksheet?
(e.g. in the running worksheet, a cell A2 as string which stores the value "B2", which could be change and re-run)
2) could the function GetValue2 be changed to a global function which works in excel outside VBA?


Pushing values
Search for a while on the web and they all point out it is impossible in excel.
but instead of pushing if it is possible to do the following in a workbook instead?
Subroutine in Worksheet A:
1) Open worksheet B
2) change certain cells
3) close worksheet B.
4) continue of on the subroutine

Sorry for such stupids questions.
Many thanks.

 

[sijpie]

I don't think you can open a closed file within a function. I tried several ways without success.

if the file is open then you can get the value using the function.

Function MyGetVal(wb As String, sh As String, ad As String)
    Dim wb1 As Workbook
    Dim ws1 As Worksheet
    Dim Val
    Set wb1 = Workbooks(wb & ".xls")
    Set ws1 = wb1.Sheets(sh)
    Val = ws1.Range(ad).Value
    Set ws1 = Nothing
    Set wb1 = Nothing
    GetVal = Val
End Function