Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
I am using Excel 2003 and want to make my code as generic as possible.
I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
WBook and WSheet are variables... you don't put variable names inside of
quote marks if you want to get at their contents (text inside of quote marks is just that... text). Try your Set statements this way... Set MstrInvList = Workbooks(WBook) Set AllInvSht = Worksheets(WSheet) -- Rick (MVP - Excel) "GEdwards" wrote in message ... I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
You are very close;
When you pass WBook as a parameter, Excel will use the string that it represents. When you pass "WBook", then Excel simply takes that string, and does not know that you are trying to refer to a variable. Set MstrInvList = Workbooks(WBook) and Set AllInvSht = Worksheets(WSheet) HTH, Keith "GEdwards" wrote: I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
I tried that but I get
Run-time error '9': Subscript out of range "ker_01" wrote: You are very close; When you pass WBook as a parameter, Excel will use the string that it represents. When you pass "WBook", then Excel simply takes that string, and does not know that you are trying to refer to a variable. Set MstrInvList = Workbooks(WBook) and Set AllInvSht = Worksheets(WSheet) HTH, Keith "GEdwards" wrote: I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
As I posted to ker_01, I tried that but I get
Run-time error '9': Subscript out of range "Rick Rothstein" wrote: WBook and WSheet are variables... you don't put variable names inside of quote marks if you want to get at their contents (text inside of quote marks is just that... text). Try your Set statements this way... Set MstrInvList = Workbooks(WBook) Set AllInvSht = Worksheets(WSheet) -- Rick (MVP - Excel) "GEdwards" wrote in message ... I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
Dim MstrInvList As Workbook
Dim AllInvSht As Worksheet set WBook = workbooks(Range("S2").value) set WSheet = wbook.worksheets(Range("R2").value) But if you get that subscript out of range, then the names in S2 and R2 don't match up with the names of the open workbooks. I'd do some testing and also qualify those ranges! Dim MstrInvList As Workbook Dim AllInvSht As Worksheet set wbook = nothing on error resume next set WBook = workbooks(activesheet.Range("S2").value) on error goto 0 if wbook is nothing then msgbox "There is no open workbook with that name!" exit sub '??? end if set wsheet = nothing on error resume next set WSheet = wbook.worksheets(activesheet.Range("R2").value) on error goto 0 if wsheet is nothing then msgbox "The workbook was found, but it didn't have a sheet with that name!" exit sub '??? end if ======= I used the activesheet, but you could use something like: set WSheet _ = wbook.worksheets(thisworkbook.worksheets("Sheet999 ").Range("R2").value) GEdwards wrote: I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of Cell Values Instead of Hard Coding
Dave,
You were right in having me find what the active Workbook/sheet were. Once I set it to the correct one, all is fine. Thanks to all. Garth "Dave Peterson" wrote: Dim MstrInvList As Workbook Dim AllInvSht As Worksheet set WBook = workbooks(Range("S2").value) set WSheet = wbook.worksheets(Range("R2").value) But if you get that subscript out of range, then the names in S2 and R2 don't match up with the names of the open workbooks. I'd do some testing and also qualify those ranges! Dim MstrInvList As Workbook Dim AllInvSht As Worksheet set wbook = nothing on error resume next set WBook = workbooks(activesheet.Range("S2").value) on error goto 0 if wbook is nothing then msgbox "There is no open workbook with that name!" exit sub '??? end if set wsheet = nothing on error resume next set WSheet = wbook.worksheets(activesheet.Range("R2").value) on error goto 0 if wsheet is nothing then msgbox "The workbook was found, but it didn't have a sheet with that name!" exit sub '??? end if ======= I used the activesheet, but you could use something like: set WSheet _ = wbook.worksheets(thisworkbook.worksheets("Sheet999 ").Range("R2").value) GEdwards wrote: I am using Excel 2003 and want to make my code as generic as possible. I am very junior with this but I know proper coding is ... Dim MstrInvList As Workbook Dim AllInvSht As Worksheet Set MstrInvList = Workbooks("Invoices & Work Estimates.xls") MstrInvList.Activate Set AllInvSht = Worksheets("Invoices & Work Estimates") AllInvSht.Activate But I would like something like this... where cell S2 contains "Invoices & Work Estimates" and cell R2 contains "Invoices & Work Estimates.xls" The SET lines below fail. Is there a way around this? Can cell values used in place of hard coding the workbook, worksheet names, etc? Dim MstrInvList As Workbook Dim AllInvSht As Worksheet WBook = Range("S2") WSheet = Range("R2") Set MstrInvList = Workbooks("WBook") MstrInvList.Activate Set AllInvSht = Worksheets("WSheet") AllInvSht.Activate Others that I need to consider are working with these others... Set DestWB = Workbooks.Open("E:\Directory1\TempWorkSheet.xls") Set SourceRange = ThisWorkbook.Sheets("Fun With Excel").Range("K8:W8") Set DestSh = DestWB.Worksheets("Invoices & Work Estimates") Any and all help would be appreciated. Thanks in advance! -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you instantiate classes without hard-coding class names?? | Excel Programming | |||
hard coding a cell | Excel Discussion (Misc queries) | |||
User-changeable paths into Excel - avoid hard coding | Excel Programming | |||
Hard-Coding a File Location | Excel Programming |