Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about assigning DIM and such
I thought that this would be a great process of setting up everything at
front and declaring then assigning. But when it gets to the g line and says it subscript out of range error. Any idea's a-Sub getnewnum() b-Dim QteFile As Workbook 'This is the source of all quotes c-Dim ExlPpsl As Workbook 'This is the downloaded file d-Dim QteSht As Worksheet 'Previous Number worksheet e-Dim AllQtes As Worksheet 'QteSrc worksheet f-Dim Ppsl As Worksheet '2. Proposal Data worksheet g-Set QteFile = Workbooks("C:\Documents and Settings\jason.hopson\My h-Documents\A-dec Projects\ICC\ICC KAIZEN EVENT\QuoteNumber.xls") i-Set ExlPpsl = ActiveWorkbook k-Set QteSht = QteFile.Worksheets("Previous Number") 'This is currently the l-same as SrcNumSht m-Set AllQtes = QteFile.Worksheets("QteSrc") n-Set Ppsl = ExlPpsl.Worksheets("2. Proposal Data") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about assigning DIM and such
When you access a workbook via the Workbooks collection, use just
the filename without the directory path. E.g., Set QteFile = Workbooks("QuoteNumber.xls") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Rookie_User" wrote in message ... I thought that this would be a great process of setting up everything at front and declaring then assigning. But when it gets to the g line and says it subscript out of range error. Any idea's a-Sub getnewnum() b-Dim QteFile As Workbook 'This is the source of all quotes c-Dim ExlPpsl As Workbook 'This is the downloaded file d-Dim QteSht As Worksheet 'Previous Number worksheet e-Dim AllQtes As Worksheet 'QteSrc worksheet f-Dim Ppsl As Worksheet '2. Proposal Data worksheet g-Set QteFile = Workbooks("C:\Documents and Settings\jason.hopson\My h-Documents\A-dec Projects\ICC\ICC KAIZEN EVENT\QuoteNumber.xls") i-Set ExlPpsl = ActiveWorkbook k-Set QteSht = QteFile.Worksheets("Previous Number") 'This is currently the l-same as SrcNumSht m-Set AllQtes = QteFile.Worksheets("QteSrc") n-Set Ppsl = ExlPpsl.Worksheets("2. Proposal Data") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused about assigning DIM and such
You have the right idea but you want something more like this...
Sub getnewnum() Dim QteFile As Workbook 'This is the source of all quotes Dim ExlPpsl As Workbook 'This is the downloaded file Dim QteSht As Worksheet 'Previous Number worksheet Dim AllQtes As Worksheet 'QteSrc worksheet Dim Ppsl As Worksheet '2. Proposal Data worksheet on error resume next 'try to set the object to the open workbook QteFile = Workbooks("QuoteNumber.xls") on error goto 0 if QteFile is nothing 'Check if successful 'not successful so open book Set QteFile = Workbooks.open ("C:\Documents and Settings\jason.hopson\My Documents\A-dec Projects\ICC\ICC KAIZEN EVENT\QuoteNumber.xls") 'This is now the active workbook end if Set ExlPpsl = ActiveWorkbook Set QteSht = QteFile.Worksheets("Previous Number") 'This is currently the same as SrcNumSht Set AllQtes = QteFile.Worksheets("QteSrc") Set Ppsl = ExlPpsl.Worksheets("2. Proposal Data") Be careful using active workbook (I pretty much never use it) as it is dependant on what is active when this code runs. Try to set expicitly using the workbook name or reference ThisWorkbook. -- HTH... Jim Thomlinson "Rookie_User" wrote: I thought that this would be a great process of setting up everything at front and declaring then assigning. But when it gets to the g line and says it subscript out of range error. Any idea's a-Sub getnewnum() b-Dim QteFile As Workbook 'This is the source of all quotes c-Dim ExlPpsl As Workbook 'This is the downloaded file d-Dim QteSht As Worksheet 'Previous Number worksheet e-Dim AllQtes As Worksheet 'QteSrc worksheet f-Dim Ppsl As Worksheet '2. Proposal Data worksheet g-Set QteFile = Workbooks("C:\Documents and Settings\jason.hopson\My h-Documents\A-dec Projects\ICC\ICC KAIZEN EVENT\QuoteNumber.xls") i-Set ExlPpsl = ActiveWorkbook k-Set QteSht = QteFile.Worksheets("Previous Number") 'This is currently the l-same as SrcNumSht m-Set AllQtes = QteFile.Worksheets("QteSrc") n-Set Ppsl = ExlPpsl.Worksheets("2. Proposal Data") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
confused | Excel Worksheet Functions | |||
I'm still confused | Excel Worksheet Functions | |||
Very confused.... | Excel Discussion (Misc queries) | |||
:S confused :S | Charts and Charting in Excel | |||
I'm Confused........................................ | Excel Programming |