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
"BEEJAY" wrote in message
...
| 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
| "BEEJAY" wrote in message
| ...
| | 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
| | wrote:
| |
| | 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?
| |
|
|