workbook referencing - how to!
 

Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)

workbook referencing - how to!
 

Non-VBA:

Open both workbooks, then:
Copy cell A1 on worksheet 1 in workbook1.xls
Paste Value or Link in cell A1 on worksheet 2 in workbook2.xls


VBA:

Sub CopyValue()
'Change the names as appropriate
Workbooks("workbook 2.xls").Worksheets("Worksheet 2").Range("A1").Value = _
Workbooks("workbook 1.xls").Worksheets("Worksheet 1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)

workbook referencing - how to!
 

On Wed, 7 Dec 2005, Bernie Deitrick wrote:


VBA:

Sub CopyValue()
'Change the names as appropriate
Workbooks("workbook 2.xls").Worksheets("Worksheet 2").Range("A1").Value = _
Workbooks("workbook 1.xls").Worksheets("Worksheet 1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

I had the same problem, and started in fact with what you propose here,
but it didn't work (Subscript out of range).

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

Maybe I ask my real question (only VBA):
I would need to collect the values of one (or very few) cell(s) from
several dozens of closed input-workbooks and write these values into
one single sheet of the "output" workbooks, hopefully *without* opening
and closing all these input-workbooks explicitely.

Is this possible? If not, was is the most efficient way of opening
(or activating) and closing the input-workbooks?

Thank you very much in advance,

Bernd





wrote in message
oups.com...
Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)

workbook referencing - how to!
 

Bernd,

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

That's why I preceeded the code with the note "Open both workbooks, then:"

To create links to multiple workbooks without opening them, try the macro below, modified to suit
your specifics. As wtitten, the code will link to cell A1 from Sheet1 - change the A1 to the cell
that you want, and the sheet name as appropriate.

Also change the .Lookin line to your folder path and name to the folder where the files are stored.

If you need help modifying this code, post back.

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Bernd Pollermann" wrote in message
n.ch...
On Wed, 7 Dec 2005, Bernie Deitrick wrote:


VBA:

Sub CopyValue()
'Change the names as appropriate
Workbooks("workbook 2.xls").Worksheets("Worksheet 2").Range("A1").Value = _
Workbooks("workbook 1.xls").Worksheets("Worksheet 1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

I had the same problem, and started in fact with what you propose here,
but it didn't work (Subscript out of range).

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

Maybe I ask my real question (only VBA):
I would need to collect the values of one (or very few) cell(s) from
several dozens of closed input-workbooks and write these values into
one single sheet of the "output" workbooks, hopefully *without* opening
and closing all these input-workbooks explicitely.

Is this possible? If not, was is the most efficient way of opening
(or activating) and closing the input-workbooks?

Thank you very much in advance,

Bernd





wrote in message
oups.com...
Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)

workbook referencing - how to!
 

Hi,


Thanks to everyone who has provided info on this so far.

The code looks interesting and i think i can edit the relevant bits -
path and workbook & sheet actual names, as needed...However where do
you put this code?

I know how to get to the VBA editor...but i am wondering if it is as
simple as pasting into the editor at the location you arrive at once
the editor is invoked when you are in the target cell?

plus how due you run this code?

thanks to you all again...






Bernie Deitrick wrote:
Bernd,

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

That's why I preceeded the code with the note "Open both workbooks, then:"

To create links to multiple workbooks without opening them, try the macro below, modified to suit
your specifics. As wtitten, the code will link to cell A1 from Sheet1 - change the A1 to the cell
that you want, and the sheet name as appropriate.

Also change the .Lookin line to your folder path and name to the folder where the files are stored.

If you need help modifying this code, post back.

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Bernd Pollermann" wrote in message
n.ch...
On Wed, 7 Dec 2005, Bernie Deitrick wrote:


VBA:

Sub CopyValue()
'Change the names as appropriate
Workbooks("workbook 2.xls").Worksheets("Worksheet 2").Range("A1").Value = _
Workbooks("workbook 1.xls").Worksheets("Worksheet 1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

I had the same problem, and started in fact with what you propose here,
but it didn't work (Subscript out of range).

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

Maybe I ask my real question (only VBA):
I would need to collect the values of one (or very few) cell(s) from
several dozens of closed input-workbooks and write these values into
one single sheet of the "output" workbooks, hopefully *without* opening
and closing all these input-workbooks explicitely.

Is this possible? If not, was is the most efficient way of opening
(or activating) and closing the input-workbooks?

Thank you very much in advance,

Bernd





wrote in message
oups.com...
Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)

workbook referencing - how to!
 

Steve,

Try reading:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
Hi,


Thanks to everyone who has provided info on this so far.

The code looks interesting and i think i can edit the relevant bits -
path and workbook & sheet actual names, as needed...However where do
you put this code?

I know how to get to the VBA editor...but i am wondering if it is as
simple as pasting into the editor at the location you arrive at once
the editor is invoked when you are in the target cell?

plus how due you run this code?

thanks to you all again...






Bernie Deitrick wrote:
Bernd,

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

That's why I preceeded the code with the note "Open both workbooks, then:"

To create links to multiple workbooks without opening them, try the macro below, modified to suit
your specifics. As wtitten, the code will link to cell A1 from Sheet1 - change the A1 to the
cell
that you want, and the sheet name as appropriate.

Also change the .Lookin line to your folder path and name to the folder where the files are
stored.

If you need help modifying this code, post back.

HTH,
Bernie
MS Excel MVP

Sub CreateLinksToMulitpleFiles()
Dim MyFormula As String
Dim myCount As Integer
myCount = 1

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\Excel"
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Generate myFormula through string manipulation
MyFormula = "='" & .LookIn & "\[" & _
Application.Substitute(.FoundFiles(i), .LookIn & "\", "") _
& "]Sheet1'!A1"
'Set cell formula
Cells(myCount, 1).Value = .FoundFiles(i)
Cells(myCount, 2).Formula = MyFormula
myCount = myCount + 1
Next i
End If
End With
End Sub



"Bernd Pollermann" wrote in message
n.ch...
On Wed, 7 Dec 2005, Bernie Deitrick wrote:


VBA:

Sub CopyValue()
'Change the names as appropriate
Workbooks("workbook 2.xls").Worksheets("Worksheet 2").Range("A1").Value = _
Workbooks("workbook 1.xls").Worksheets("Worksheet 1").Range("A1").Value
End Sub

HTH,
Bernie
MS Excel MVP

I had the same problem, and started in fact with what you propose here,
but it didn't work (Subscript out of range).

Now, by pure chance, I happened to have the input-workbook open, and
it seems to work. Is that the solution?

Maybe I ask my real question (only VBA):
I would need to collect the values of one (or very few) cell(s) from
several dozens of closed input-workbooks and write these values into
one single sheet of the "output" workbooks, hopefully *without* opening
and closing all these input-workbooks explicitely.

Is this possible? If not, was is the most efficient way of opening
(or activating) and closing the input-workbooks?

Thank you very much in advance,

Bernd





wrote in message
oups.com...
Hi,


please can someone tell me how to take info from

cell A1 on
worksheet 1
in workbook1.xls

and put it in:

cell A1 on
worksheet 2
in workbook2.xls

?

thanks

(does this have anything to do with VBA...or is there an easy way to do
this? thanks...)