I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCe ll).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

You can use ThisWorkbook for the workbook containing the code.

"kirkm" wrote:


I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCe ll).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

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)

--
Regards,

OssieMac


"kirkm" wrote:


I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCe ll).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

kirkm;259330 Wrote:
I'm using the following function:

Function ReadCell(msheet, mCell)
'ReadCell = ActiveWorkbook.Worksheets(msheet).Range(mCell).Val ue
ReadCell = Workbooks("this.xls").Worksheets(msheet).Range(mCe ll).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 the
workbook the code is in whenever you have 2 or more workbooks open at
the same time. Prefix any code that you want to refer to your workbook
(the one with the code in it) with ThisWorkbook. Otherwise VBA assumes
you are referring to whichever workbook is active.

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=72419

Thank you all very much !

It's working perfectly, you guys are great ! :)

Cheers - Kirk