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]

First of all these are not stupid questions. A stupid question is one that asks what just has been told...

Pulling values:
The Cell B3 is fixed in this part of the code:

Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension, _
    Cells(3, 3), Cells(2, 2).Address(0, 0))

That passes the address of Cell(2,2) to the function GetValue2. Cell(2,2) is B2 (second row second column)

So if you want the value from G44, then you can either use:

Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension,  _
    Cells(3, 3), Cells(44,7).Address(0, 0))

or

Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension, _
    Cells(3, 3), Range("G44").Address)

Pushing data

What you propose is the correct way to do it.
You can either ask the user to select a workbook, or if it is always the same then hardcode which book to open.

Search this forum on opening workbooks through vba, and you will get plenty examples

 

[pennylam]

Thanks for the quick reply, sijpie.

Sorry for making myself not clear enough.
I can understand the "fixed address" could be changed in the scripts.
But could that "fixed address" be extracted from the master spreasheet as variable as well?

for example, in the master spreadsheets.
A1 = where the result is stored after running the macro
A2 = a string as required address, e.g. "B2","G44"

the macro is then run with cell A2 is a input,
and return the value into A1 with the cell value B2 or G44 in the data worksheet.


Originally I think the private function can be changed into a function easily but that's not my case,
although i can image if it become a global function in excel the worksheet will be running very slow in extracting data all the times...


And thanks for the guide in opening worksheets as well,
will start seacrhing this useful forum.

 

[sdc1234]

this will work even if the master report is a separate form all by itself...

thx

 

[pennylam]

Please let me present my problem in another way

In the master worksheet:
cell A1 => output cell
cell C2 => "data" (filename of data worksheet is data.xls)
cell C3 => "sheet1" (sheetname is sheet1 in data.xls)
cell C4 => "E6" (name of cell i want to read in the data worksheet)

Target:
Read cell "E6" within "sheet1" in "data.xls"

the below script could work, as the target address is fixed as E6
Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension, Cells(3, 3), Range("E6").Address)

what i tried is below, which didn't work
temp_string = cells(C4)
Cells(1, 1) = GetValue2(ThisWorkbook.path, Cells(2, 3) & extension, Cells(3, 3), Range(temp_string).Address)

in short,
I don't know how to input a string in a excel as an address in VBA...

many thanks

BTW, how can i paste spreadsheet like tables in this forum?

 

[sijpie]

To read a value form excel and use in your code is simple:

sub ReadValue()
   dim MyVar
   MyVar = Range("A2").value
   msgbox MyVar
end Sub

So for your example something like:

   Dim WBook as string, WSheet as String, Rng as String
 
   WBook = Range("C2").Value
   WSheet = Range("C3").Value
   Rng = Range("C4").Value
 
  Cells(1, 1) = GetValue2(ThisWorkbook.path, WBook & extension,  _
    WSheet, Range(Rng).Address)

 

[pennylam]

Thank you, sijpie !!

 

[pennylam]

Open more question before i give up in the pulling value UDF.

I amended the private function getvalue2 to change it into a workbook function.

Again here are the conditions in the master worksheet
cell C2: "Data_file" <- name of workbook
cell C3: "Sheet1" <-name of worksheet
cell C4: "C2" <-name of target cell

cell C9: "=GetValue(C2,C3,C4)" <- calling UDF for extracting value

here is the UDF GetValue

Function GetValue(file, sheet, range_ref)

Dim arg As String

extension = ".xlsx"

path = ThisWorkbook.path
If Right(path, 1) <> "\" Then
path = path & "\"
End If

If Dir(path & file & extension) = "" Then
GetValue = "-"
Exit Function
End If

rng = range_ref.Value

arg = "'" & path & "[" & file & extension & "]" & sheet & "'!" & Range(rng).Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)

End Function

Compared to the previous subroutine,
the string "arg" in the UDF is exactly the same with the one in the subroutine.
but excel just return me a "#VALUE!" to the UDF

So, does the VBA function just work in private functions?

Thanks

 

[sijpie]

Well you have declared the function Getvalue2() as a private function. That means it is only visible to the other subs & functions in the same module.

Try it again after deleting the word Private in front of Function

 

[pennylam]

Hi sijpie,

How about the "GetValue" (not "GetValue2") the function i attached in my previous post?

Thanks.