Getting values from anotjer workbook

[Miikka Hamalainen]

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

[Scott buckwalter]

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
>
>
>

[Miikka Hamalainen]

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" <[email protected]> kirjoitti
viestissä:[email protected]...
> 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
> >
> >
> >

[Dave Peterson]

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

[[email protected]]

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]

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



[email protected] 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

[[email protected]]

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]

You dropped the 4th parm.

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



[email protected] 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

[Miikka Hamalainen]

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" <[email protected]> kirjoitti
viestissä:[email protected]...
> You dropped the 4th parm.
>
> MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
> myLocations(iCtr)(3), myLocations(iCtr)(4))
>
>
>
> [email protected] 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]

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" <[email protected]> kirjoitti
> viestissä:[email protected]...
> > You dropped the 4th parm.
> >
> > MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
> > myLocations(iCtr)(3), myLocations(iCtr)(4))
> >
> >
> >
> > [email protected] 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]

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" <[email protected]> kirjoitti
> > viestissä:[email protected]...
> > > You dropped the 4th parm.
> > >
> > > MsgBox GetValue(myLocations(iCtr)(1), myLocations(iCtr)(2), _
> > > myLocations(iCtr)(3), myLocations(iCtr)(4))
> > >
> > >
> > >
> > > [email protected] 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