I need to get some data from another workbook, could anyone give me a hand
with this?


The following is what I kind of figured would work but I am unsure of the
syntax for addressing the actual cell the following calls for the data in
C:\Myfilename.xlsHandicap. This is obviously not the correct sytax.

'Dim Location As String
'Location = Range("ReadFromFilename").Text & "Handicap"
'Range("Handicap") = Range(Location)

I have searched for this and have had no luck.

Thanks In Advance,
Dave Marden

Dave:

The excel workbook is C:\Myfilename.xls
The excel sheet is Handicap
The cell is, well lets assume A1.

To get the value from the above we do this assuming that the workbook is open:

workbooks("Myfilename.xls").worksheets("Handicap") .range("A1")

So we could put the value in the a cell with

workbooks("book1.xls").worksheets("sheet1").range( "B3") =
workbooks("Myfilename.xls").worksheets("Handicap") .range("A1")

If the workbook is not open you can paste the link into workbook and using
linking:

activesheet.range("B3").formula= "'[c:\Myfilename.xls]Handicap'!A1"

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Dave Marden" wrote:

I need to get some data from another workbook, could anyone give me a hand
with this?


The following is what I kind of figured would work but I am unsure of the
syntax for addressing the actual cell the following calls for the data in
C:\Myfilename.xlsHandicap. This is obviously not the correct sytax.

'Dim Location As String
'Location = Range("ReadFromFilename").Text & "Handicap"
'Range("Handicap") = Range(Location)

I have searched for this and have had no luck.

Thanks In Advance,
Dave Marden

To get a value from another workbook, you must specify:
1. workbook name
2. sheet name
3. cell name or address

Sub demo()
Dim v As String
v = Workbooks("myfilename.xls").Sheets("Handicap").Ran ge("B2").Value
MsgBox (v)
End Sub
--
Gary's Student
gsnu200701


"Dave Marden" wrote:

I need to get some data from another workbook, could anyone give me a hand
with this?


The following is what I kind of figured would work but I am unsure of the
syntax for addressing the actual cell the following calls for the data in
C:\Myfilename.xlsHandicap. This is obviously not the correct sytax.

'Dim Location As String
'Location = Range("ReadFromFilename").Text & "Handicap"
'Range("Handicap") = Range(Location)

I have searched for this and have had no luck.

Thanks In Advance,
Dave Marden

Actually what I am trying to do is a little deaper than this.

Spreadsheet name that is the data handler = Data Gatherer For Scoreboard.xls
Sheet Name = Sheet1
Range Name = Handicap

Spreadsheet Data is supposed to be coming from = ReadFromFilename
Sheet Name = Competitors A-Z
Range = D29

Spreadsheet Data is supposed to be going to = WriteToFilename
Sheet Name = Competitors A-Z
Range = D29

The Following is the code I worked out using your suggestions. The Data
Gatherer spreadsheet is getting the file path, name, and extension. This is
put into a cell with the name of ReadFromFilename. I would think this
should work but I am getting a ("Runtime Error 9" Subscript out of range
error). Could anyone help me out with what is wrong with my sytanx? I
would assume it is the part where I write
"Workbooks(Range("ReadFromFilename").Text)" but I would think this should
work.

Private Sub cmdRead_Click()
Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") =
Workbooks(Range("ReadFromFilename").Text).Workshee ts("Competitors
A-Z").Range("D29")
End Sub

Private Sub cmdWrite_Click()
Workbooks(Range("WriteToFilename").Text).Worksheet s("Competitors
A-Z").Range("D29") = Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap")
End Sub


Any Help Would Be Appreciated,
Dave Marden

Dave

Assuming that the workbooks are open:

Private Sub cmdRead_Click()

Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap") = _
Workbooks("ReadFromFilename.xls").Worksheets( _
"Competitors A-Z").Range("D29")
End Sub

Private Sub cmdWrite_Click()
Workbooks("WriteToFilename.xls" _
).Worksheets("Competitors A-Z").Range("D29") = _
Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap")

End Sub

But...I think that you are making a it more difficult than it should be.

I'm a little unsure on what you are doing at the start and maybe this will
work but not sure.

Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap")

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Dave Marden" wrote:

Actually what I am trying to do is a little deaper than this.

Spreadsheet name that is the data handler = Data Gatherer For Scoreboard.xls
Sheet Name = Sheet1
Range Name = Handicap

Spreadsheet Data is supposed to be coming from = ReadFromFilename
Sheet Name = Competitors A-Z
Range = D29

Spreadsheet Data is supposed to be going to = WriteToFilename
Sheet Name = Competitors A-Z
Range = D29

The Following is the code I worked out using your suggestions. The Data
Gatherer spreadsheet is getting the file path, name, and extension. This is
put into a cell with the name of ReadFromFilename. I would think this
should work but I am getting a ("Runtime Error 9" Subscript out of range
error). Could anyone help me out with what is wrong with my sytanx? I
would assume it is the part where I write
"Workbooks(Range("ReadFromFilename").Text)" but I would think this should
work.

Private Sub cmdRead_Click()
Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") =
Workbooks(Range("ReadFromFilename").Text).Workshee ts("Competitors
A-Z").Range("D29")
End Sub

Private Sub cmdWrite_Click()
Workbooks(Range("WriteToFilename").Text).Worksheet s("Competitors
A-Z").Range("D29") = Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap")
End Sub


Any Help Would Be Appreciated,
Dave Marden

You are writing down that the filename is a constant in the code, as in
ReadFromFilename.xls. I am trying to obtain the filename from the user than
I store that filename in the range "ReadFromFilename". Then I am trying to
use this filename from that range in determining the filename to read from.

The user click on a button which opens a file open dialog box, I obtain the
name from that. This filename I then put into the range ReadFromFilename.
I then use this name to determine which file the user wants me to obtain
values from. I also obtain the WriteToFilename so that I can grab data from
a newer version of my program. I am trying to make it easier for users of
my program to migrate from an earlier version to a newer version of my
program.

I hope this clears things up for you.

Dave Marden


"Martin Fishlock" wrote in message
...
Dave

Assuming that the workbooks are open:

Private Sub cmdRead_Click()

Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap") = _
Workbooks("ReadFromFilename.xls").Worksheets( _
"Competitors A-Z").Range("D29")
End Sub

Private Sub cmdWrite_Click()
Workbooks("WriteToFilename.xls" _
).Worksheets("Competitors A-Z").Range("D29") = _
Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap")

End Sub

But...I think that you are making a it more difficult than it should be.

I'm a little unsure on what you are doing at the start and maybe this will
work but not sure.

Workbooks("Data Gatherer For Scoreboard.xls" _
).Worksheets("Sheet1").Range("Handicap")

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Dave Marden" wrote:

Actually what I am trying to do is a little deaper than this.

Spreadsheet name that is the data handler = Data Gatherer For
Scoreboard.xls
Sheet Name = Sheet1
Range Name = Handicap

Spreadsheet Data is supposed to be coming from = ReadFromFilename
Sheet Name = Competitors A-Z
Range = D29

Spreadsheet Data is supposed to be going to = WriteToFilename
Sheet Name = Competitors A-Z
Range = D29

The Following is the code I worked out using your suggestions. The Data
Gatherer spreadsheet is getting the file path, name, and extension. This
is
put into a cell with the name of ReadFromFilename. I would think this
should work but I am getting a ("Runtime Error 9" Subscript out of range
error). Could anyone help me out with what is wrong with my sytanx? I
would assume it is the part where I write
"Workbooks(Range("ReadFromFilename").Text)" but I would think this should
work.

Private Sub cmdRead_Click()
Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap") =
Workbooks(Range("ReadFromFilename").Text).Workshee ts("Competitors
A-Z").Range("D29")
End Sub

Private Sub cmdWrite_Click()
Workbooks(Range("WriteToFilename").Text).Worksheet s("Competitors
A-Z").Range("D29") = Workbooks("Data Gatherer For
Scoreboard.xls").Worksheets("Sheet1").Range("Handi cap")
End Sub


Any Help Would Be Appreciated,
Dave Marden