Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Good afternoon, all!
I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").Formula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Why chase your tail:
if thisworkbook.Names("TeamName").RefersToRange.Text < _ "Consolidating - please wait..." then if TeamName is a sheet level name, then use "DataBase!TeamName" rather than just "TeamName" Using the Text property insures a string to string comparison. doing ConsolidatorWorkBookName = ThisWorkbook.Name then using the variable is like saying "Peter, what's your first name?" You always have access to the ThisWorkbook object. So you can always get the name. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
That should work okay, it did with my names. Is TeamName a multi-cell range,
if so that may be the problem. -- HTH RP (remove nothere from the email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Bob,
No, just a single cell... The problem is, all my workbooks are based on the same workbook, using SaveAs, so they all contain the same variable declarations - so, as soon as I open the workbook to be consolidated, all the contents of my variables get set back to empty! Doh! Cheers Pete "Bob Phillips" wrote: That should work okay, it did with my names. Is TeamName a multi-cell range, if so that may be the problem. -- HTH RP (remove nothere from the email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Tom,
The problem is, all my workbooks are based on the same workbook, using SaveAs, so they all contain the same variable declarations - so, as soon as I open the workbook to be consolidated, all the contents of my variables get set back to empty! I need to always know the name of the first workbook I started with, so I can check in the "Teamcell" cell to see if a consolidation is taking place (the "Consolidating..." message is pasted there when the consolidate macro begins), and this, NOT run the code (as shown in my first post) I know I should somehow create my Save As files so that they don't have any macro sheets (and thus don't reset the values in the variables in the consolidating workbook every time they open) but I don't know how to do this. I think I probably need an add in that is independent of any workbook being used in the consolidation process. Thanks Pete "Tom Ogilvy" wrote: Why chase your tail: if thisworkbook.Names("TeamName").RefersToRange.Text < _ "Consolidating - please wait..." then if TeamName is a sheet level name, then use "DataBase!TeamName" rather than just "TeamName" Using the Text property insures a string to string comparison. doing ConsolidatorWorkBookName = ThisWorkbook.Name then using the variable is like saying "Peter, what's your first name?" You always have access to the ThisWorkbook object. So you can always get the name. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
If you set your variable with
varname = Thisworkbook.Name then it isn't going to be visible to any workbook other than the workbook running the code and it will refer to the workbook running the code. In otherwords, if I set a variable in Book1, it won't be visible to code in book2 or code in book3. So I suspect you are getting an error because you are using a local version of your variable and it is empty. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, The problem is, all my workbooks are based on the same workbook, using SaveAs, so they all contain the same variable declarations - so, as soon as I open the workbook to be consolidated, all the contents of my variables get set back to empty! I need to always know the name of the first workbook I started with, so I can check in the "Teamcell" cell to see if a consolidation is taking place (the "Consolidating..." message is pasted there when the consolidate macro begins), and this, NOT run the code (as shown in my first post) I know I should somehow create my Save As files so that they don't have any macro sheets (and thus don't reset the values in the variables in the consolidating workbook every time they open) but I don't know how to do this. I think I probably need an add in that is independent of any workbook being used in the consolidation process. Thanks Pete "Tom Ogilvy" wrote: Why chase your tail: if thisworkbook.Names("TeamName").RefersToRange.Text < _ "Consolidating - please wait..." then if TeamName is a sheet level name, then use "DataBase!TeamName" rather than just "TeamName" Using the Text property insures a string to string comparison. doing ConsolidatorWorkBookName = ThisWorkbook.Name then using the variable is like saying "Peter, what's your first name?" You always have access to the ThisWorkbook object. So you can always get the name. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Tom,
The REAL problem is, in my Workbook_Deactivate macro, I call a routine which changes some screen options, resets the Excel menu bar etc (so that if any clever user hits Ctrl+F6, the Deactivate macro runs and they get normal, uncusomised Excel), which in turn clears the clipboard (see my other posting that you replied to). When I consolidate, I open other workbooks, all of which contain identical macro sheets and variable declarations, because when they're created, using save As within VBA, they're based on the original workbook. As all these variable declarations are identical in each workbook, as soon as my consolidate macro opens another workbook, all my variables, along with their contents are redeclared, and thus emptied. Because of the clipboard emptying effect, I wanted to say "If I'm consolidating, don't run the screen reset in Workbook_Deactivate"), but as I can't store ANYTHING without it being reset, I'm b******d, so to speak. I think I'll have to think the whole thing out again, but, once again, thanks for your interest and advice! Regards Pete "Tom Ogilvy" wrote: If you set your variable with varname = Thisworkbook.Name then it isn't going to be visible to any workbook other than the workbook running the code and it will refer to the workbook running the code. In otherwords, if I set a variable in Book1, it won't be visible to code in book2 or code in book3. So I suspect you are getting an error because you are using a local version of your variable and it is empty. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, The problem is, all my workbooks are based on the same workbook, using SaveAs, so they all contain the same variable declarations - so, as soon as I open the workbook to be consolidated, all the contents of my variables get set back to empty! I need to always know the name of the first workbook I started with, so I can check in the "Teamcell" cell to see if a consolidation is taking place (the "Consolidating..." message is pasted there when the consolidate macro begins), and this, NOT run the code (as shown in my first post) I know I should somehow create my Save As files so that they don't have any macro sheets (and thus don't reset the values in the variables in the consolidating workbook every time they open) but I don't know how to do this. I think I probably need an add in that is independent of any workbook being used in the consolidation process. Thanks Pete "Tom Ogilvy" wrote: Why chase your tail: if thisworkbook.Names("TeamName").RefersToRange.Text < _ "Consolidating - please wait..." then if TeamName is a sheet level name, then use "DataBase!TeamName" rather than just "TeamName" Using the Text property insures a string to string comparison. doing ConsolidatorWorkBookName = ThisWorkbook.Name then using the variable is like saying "Peter, what's your first name?" You always have access to the ThisWorkbook object. So you can always get the name. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
I can't think of any situation where opening another workbook would affect
the variables in an existing workbook unless a reset command or perhaps a stop command is issued. Just because one workbook is a copy of the original workbook should have no effect. Variables, even public variables are local to the workbook. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, The REAL problem is, in my Workbook_Deactivate macro, I call a routine which changes some screen options, resets the Excel menu bar etc (so that if any clever user hits Ctrl+F6, the Deactivate macro runs and they get normal, uncusomised Excel), which in turn clears the clipboard (see my other posting that you replied to). When I consolidate, I open other workbooks, all of which contain identical macro sheets and variable declarations, because when they're created, using save As within VBA, they're based on the original workbook. As all these variable declarations are identical in each workbook, as soon as my consolidate macro opens another workbook, all my variables, along with their contents are redeclared, and thus emptied. Because of the clipboard emptying effect, I wanted to say "If I'm consolidating, don't run the screen reset in Workbook_Deactivate"), but as I can't store ANYTHING without it being reset, I'm b******d, so to speak. I think I'll have to think the whole thing out again, but, once again, thanks for your interest and advice! Regards Pete "Tom Ogilvy" wrote: If you set your variable with varname = Thisworkbook.Name then it isn't going to be visible to any workbook other than the workbook running the code and it will refer to the workbook running the code. In otherwords, if I set a variable in Book1, it won't be visible to code in book2 or code in book3. So I suspect you are getting an error because you are using a local version of your variable and it is empty. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, The problem is, all my workbooks are based on the same workbook, using SaveAs, so they all contain the same variable declarations - so, as soon as I open the workbook to be consolidated, all the contents of my variables get set back to empty! I need to always know the name of the first workbook I started with, so I can check in the "Teamcell" cell to see if a consolidation is taking place (the "Consolidating..." message is pasted there when the consolidate macro begins), and this, NOT run the code (as shown in my first post) I know I should somehow create my Save As files so that they don't have any macro sheets (and thus don't reset the values in the variables in the consolidating workbook every time they open) but I don't know how to do this. I think I probably need an add in that is independent of any workbook being used in the consolidation process. Thanks Pete "Tom Ogilvy" wrote: Why chase your tail: if thisworkbook.Names("TeamName").RefersToRange.Text < _ "Consolidating - please wait..." then if TeamName is a sheet level name, then use "DataBase!TeamName" rather than just "TeamName" Using the Text property insures a string to string comparison. doing ConsolidatorWorkBookName = ThisWorkbook.Name then using the variable is like saying "Peter, what's your first name?" You always have access to the ThisWorkbook object. So you can always get the name. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a range within a workbook
Bob,
The problem was finally solved - the clipboard was being cleared because I was doing the equivalent of Tools Options View in VBA to change the screen display. It does this in normal Excel too, although I don't know why. Tom Ogilvy told me about application.enableevents=false, which meant that as I switched from workbook to workbook, I no longer invoked the screen changes, which were called in workbook_Activate/Deactivate, and hence, the contents of the clipboard remained intact. Thank you for your advice - I've picked up a number of useful pointers that I'll use in the future. Thanks Pete "Bob Phillips" wrote: That should work okay, it did with my names. Is TeamName a multi-cell range, if so that may be the problem. -- HTH RP (remove nothere from the email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I have a workbook that uses a VBA routine to consolidate a number of other workbooks into itself using the rough logic: open workbook to be consolidated - copy data - return to consolidating workbook - paste data - return to consolidated workbook - close - repeat as necessary for multiple files. I want to be able to run some code ONLY if the value of a particular cell in the Consolidator workbook doesn't equal a certain value. The problem is I can't find the correct syntax to reference the range. I have trapped the name of the consolidating workbook to a variable "Consolidator WorkBookName" with ConsolidatorWorkBookName = ThisWorkbook.Name and want to say something like: If Workbooks(ConsolidatorWorkBookName).sheets("Databa se").Range("TeamName").For mula < "Consolidating - please wait..." Then ScreenReset End If but this gives me a "Type Mismatch" Can anyone tell me where I'm going wrong? I've tried to create a concatenated string that includes double quotes at the beginning and the end of the workbook name, and stripping the ".xls" from the end, but to no avail. Thanks in advance Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
referencing a named range from a closed workbook | Excel Programming | |||
VBA Referencing a Named Cell Range in another Workbook | Excel Programming | |||
referencing another workbook | Excel Programming | |||
Referencing a range from another workbook | Excel Programming |