Grabbing a value from another workbook

[jernigan]

I'm convinced I'm screwing up something really basic, but I'm trying to take a value from a cell in another workbook based on a variable range. This is a macro for a worksheet called "Gholson" in a workbook called "DataMine". I know this isn't the simplest code, but I just wanted something that works:

Function CellValue(RowNumber, ColumnNumber)
Dim ValueRange as Range

ValueRange = Workbooks("Brain.xls&quot.Worksheets("Class B".xls).Cells(RowNumber,ColumnNumber)

CellValue = WorkBooks("Brain.xls&quot.Worksheets("Class B".xls).Range(ValueRange).Value

End Function

However, when I run it, I get the #VALUE! error.

Any ideas?

 

[SkipVought]

This may work better...

Function CellValue(RowNumber, ColumnNumber)

CellValue = Workbooks("Brain.xls").Worksheets("Class B.xls").Cells(RowNumber, ColumnNumber).Value

End Function

You quote mark was misplaced in Worksheets.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jernigan]

Thanks for the quick response, Skip.

Unfortunately, it still didn't work. To be honest, I didn't even mean to put .xls at the end of my worksheet name.

As I understand it, Cells is a property, not an object; does .Value still work? That's why I originally tried to use ValueRange as an intermediate.

Any more ideas ?

 

[SkipVought]

Cells is also a range object.

In general...

[aValue] = [WorkbookObject].[WorksheetObject].[CellRangeObject].Value

Your Workbook Name ought to be fully quallified with a path et al.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com