If there is some risk another user will have it open or will want to open
it, you could set the "readonly" parameter of the Workbooks.Open method to
True. I do that as a matter of course unless I know I'm making a change
that will be saved.
--
Jim
| Gentlemen (ALL)
|
| Thanks for all the info. Always amazes me how many ways one can get a
| certain thing done.
| For now, I went with opening the file in order to get at the range name
info.
| Then close it asap, so it can't interfere with any other part of the
| procedure.
| Final (working) code reads as follows:
|
| Sub WhoAreYou()
| Workbooks.Open Filename:="C:\EXCEL ADD_INS\QCNum.xls"
| If Workbooks("QCNum.xls").Worksheets("Sheet1").Range("C_C_TM_JFS")
| = "TJM" Then
| Call CC_Message1 'Opening message for required code
| Else
| Call Notxxx ' Message 'not available to you' and exits
| procedure
| End If
| End Sub
|
| Again, as always, thanks for the prompt input.
|
|
| "Jim Rech" wrote:
|
| > >>"compile Error: Sub or Function Not defined.
| >
| > There is no "workbook" function so that's probably causing this error.
| >
| > I think you're going to have to open the workbook first to do what want.
| > Then use the Workbooks method to access the open workbook.
| >
| > --
| > Jim
| > | > | Chip:
| > |
| > | I removed the dim statements and then get "compile Error: Sub or
Function
| > | Not defined.
| > |
| > | In the meantime, after further reading I tried to shorten up the code
to
| > | following
| > | (but it still gives me the same error, just mentioned)
| > | Also, to confirm, a workbook does NOT have to be Open/Active to be
able to
| > | READ it, Correct?
| > |
| > | Sub WhoAreYou()
| > | If Workbook("C:\Excel
| > | Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") = "TJMk"
| > Then
| > | Call CC_Message1 'Opening message for required code
| > | Else
| > | Call Notxxx ' Message 'not available to you' and exits
| > | procedure
| > | End If
| > | End Sub
| > |
| > | "Chip Pearson" wrote:
| > | >
| > | > There are several problems. First, you are declaring variables with
| > | > the name 'Workbook' and 'Worksheets'. These are reserved words in
| > | > Excel/VBA and using these names are variables is likely causing
| > | > problems. Delete the declarations as it seems you are not using
them.
| > | >
| > | > Cordially,
| > | > Chip Pearson
| > | > Microsoft MVP
| > | > Excel Product Group
| > | > Pearson Software Consulting, LLC
| > | >
www.cpearson.com
| > | > (email on web site)
| > | >
| > | >
| > | > On Mon, 8 Dec 2008 06:02:01 -0800, BEEJAY
| > | >
| > | > >Greetings All
| > | > >Getting error: Compile Error: Expected array in the following code:
| > | > >
| > | > >Sub WhoAreYou()
| > | > > Dim Workbook As Long
| > | > > Dim Worksheets As Long
| > | > >
| > | > >' The following WorkBook is NOT Open/Active
| > | > > If Workbook("C:\Excel
| > | > >Add_Ins\QCNum.xls").Worksheets("Sheet1").Range("C_C_TJM_JFS") =
"TJM"
| > Then
| > | > >
| > | > > Call CC_Message1 'Opening message for required code
| > | > > End If
| > | > > If Worksheets("QCNum.xls").Range("C_C_TJM_JFS") = "" Then
| > | > > Call Notxxx 'Message "not available to you" and
exits
| > procedure
| > | > > End If
| > | > >End Sub
| > | > >
| > | > >How to make this work, please?
| > | >
| >
| >
