xls name reference

[kirkm]

I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Value
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCell).Value
End Function

The remmed out line caused a problem when two instances of Excel were
open at the same time, and the replacement line referencing the
absolute xls filename fixed this.

However, if the filename isn't "this.xls" it fails. Excel knows the
filename somehow, as it's used as the main Caption. Can I get at this
somehow and use it instead?

Thanks - Kirk

 

[JLGWhiz]

You can use ThisWorkbook for the workbook containing the code.

 

[OssieMac]

Hi Kirk,

You could use the following formula (Extracted from Help in xl2007) in a
cell in the workbook and then use the cell reference as the parameter.
However, I am interested if anyone has a better way of doing it.

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

 

[Leith Ross]

I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Value
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCell).Value
End Function

The remmed out line caused a problem when two instances of Excel were
open at the same time, and the replacement line referencing the
absolute xls filename fixed this.

However, if the filename isn't "this.xls" it fails. Excel knows the
filename somehow, as it's used as the main Caption. Can I get at this
somehow and use it instead?

Thanks - Kirk

Hello Kirk,

If I understand your problem, you want to be able to reference th
workbook the code is in whenever you have 2 or more workbooks open a
the same time. Prefix any code that you want to refer to your workboo
(the one with the code in it) with ThisWorkbook. Otherwise VBA assume
you are referring to whichever workbook is active.

---------------------------------------------
Function ReadCell(msheet, mCell)
ReadCell = ThisWorkbook.Worksheets(msheet).Range(mCell).Value
End Function
--------------------------------------------

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[kirkm]

Thank you all very much !

It's working perfectly, you guys are great !

Cheers - Kirk