Hi,

I'm making statistic analyzes for school project and I have problem.

I have been trying to get values from another workbook to the one I'm making
statistical totals.

I would like to get the info straight from the workbook, without copying it
first to the main workbook.

I have made CommandButtons to the main sheet and have been trying to use
Worksheet and Workbook statements without success.

Does anyone have good tips to solve this?

Br, Miipe

Open both workbooks. From one workbook, in a cell type "=" then click on a
cell in the other workbook. Hit enter.

If you inspect the cell from in the first workbook, it will show you how to
reference the other workbook.

Scott

"Miikka Hamalainen" wrote:

Hi,

I'm making statistic analyzes for school project and I have problem.

I have been trying to get values from another workbook to the one I'm making
statistical totals.

I would like to get the info straight from the workbook, without copying it
first to the main workbook.

I have made CommandButtons to the main sheet and have been trying to use
Worksheet and Workbook statements without success.

Does anyone have good tips to solve this?

Br, Miipe

Hi,

I did this, but when I try to add this to CommandButton VBA script I get
error message

The script I'm using is following:

Worksheet("Total").Cells( 2, 12).Value = "here should be the other workbook
and its cells and values of the selected cells"

I have tried Workbook.Open-statement, but didn't work.

Br, Miipe

"Scott buckwalter" kirjoitti
...
Open both workbooks. From one workbook, in a cell type "=" then click on
a
cell in the other workbook. Hit enter.

If you inspect the cell from in the first workbook, it will show you how
to
reference the other workbook.

Scott

"Miikka Hamalainen" wrote:

Hi,

I'm making statistic analyzes for school project and I have problem.

I have been trying to get values from another workbook to the one I'm
making
statistical totals.

I would like to get the info straight from the workbook, without copying
it
first to the main workbook.

I have made CommandButtons to the main sheet and have been trying to use
Worksheet and Workbook statements without success.

Does anyone have good tips to solve this?

Br, Miipe

John Walkenbach has some code that will allow a macro (not a worksheet
function) retrieve values from a closed workbook.

http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.



Miikka Hamalainen wrote:

Hi,

I'm making statistic analyzes for school project and I have problem.

I have been trying to get values from another workbook to the one I'm making
statistical totals.

I would like to get the info straight from the workbook, without copying it
first to the main workbook.

I have made CommandButtons to the main sheet and have been trying to use
Worksheet and Workbook statements without success.

Does anyone have good tips to solve this?

Br, Miipe

--

Dave Peterson

Thanks, It works and it's much easier. I added this function to command
button and now I can run it just by clicking.

Now that it works, I have another question:
If I have many workbooks I need to get certain values to the same
worksheet, how should I do it?

Can I use this script or do I have to make totally new?

Br, Miipe

You could loop and get the values or you could just use a bunch of formulas in
another worksheet that point at the folders/workbooks/worksheets/ranges that you
want.

Those formulas would look like:
='C:\my documents\excel\[book2.xls]Sheet1'!$A$1

===

or looping in code...

Option Explicit
Option Base 1
Sub testme()

Dim myLocations As Variant
Dim iCtr As Long

myLocations = Array( _
Array("c:\my documents\excel", "book3.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book4.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book5.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book6.xls", "sheet1", "A1"))

For iCtr = LBound(myLocations) To UBound(myLocations)
MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
myLocations(iCtr)(3), myLocations(iCtr)(4))
Next iCtr

End Sub

'From John Walkenbach
Private Function GetValue(path, file, sheet, range_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(range_ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro

GetValue = ExecuteExcel4Macro(arg)

End Function



wrote:

Thanks, It works and it's much easier. I added this function to command
button and now I can run it just by clicking.

Now that it works, I have another question:
If I have many workbooks I need to get certain values to the same
worksheet, how should I do it?

Can I use this script or do I have to make totally new?

Br, Miipe

--

Dave Peterson

I get compile error "argument not optional" on GetValue

For iCtr = LBound(myLocations) To UBound(myLocations)
MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2),
myLocations(iCtr)(3))

Next iCtr

Something wrong?

Br, Miipe

You dropped the 4th parm.

MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
myLocations(iCtr)(3), myLocations(iCtr)(4))



wrote:

I get compile error "argument not optional" on GetValue

For iCtr = LBound(myLocations) To UBound(myLocations)
MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2),
myLocations(iCtr)(3))

Next iCtr

Something wrong?

Br, Miipe

--

Dave Peterson

No.

You'll need 4 parms for each value to return.

You need to specify the path, workbook name, sheet name, and cell address.

But you'll have entries in this portion:

myLocations = Array( _
Array("c:\my documents\excel", "book3.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book4.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book5.xls", "sheet1", "A1"))



Miikka Hamalainen wrote:

Yes, but what if I have only 3 files I need to get the info, then I should
have 3 parameters, right?

Br, Miipe

"Dave Peterson" kirjoitti
...
You dropped the 4th parm.

MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
myLocations(iCtr)(3), myLocations(iCtr)(4))



wrote:

I get compile error "argument not optional" on GetValue

For iCtr = LBound(myLocations) To UBound(myLocations)
MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2),
myLocations(iCtr)(3))

Next iCtr

Something wrong?

Br, Miipe

--

Dave Peterson

--

Dave Peterson

But you'll have FEWER entries in this portion:

(oops)

Dave Peterson wrote:

No.

You'll need 4 parms for each value to return.

You need to specify the path, workbook name, sheet name, and cell address.

But you'll have entries in this portion:

myLocations = Array( _
Array("c:\my documents\excel", "book3.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book4.xls", "sheet1", "A1"), _
Array("c:\my documents\excel", "book5.xls", "sheet1", "A1"))

Miikka Hamalainen wrote:

Yes, but what if I have only 3 files I need to get the info, then I should
have 3 parameters, right?

Br, Miipe

"Dave Peterson" kirjoitti
...
You dropped the 4th parm.

MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
myLocations(iCtr)(3), myLocations(iCtr)(4))



wrote:

I get compile error "argument not optional" on GetValue

For iCtr = LBound(myLocations) To UBound(myLocations)
MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2),
myLocations(iCtr)(3))

Next iCtr

Something wrong?

Br, Miipe

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson