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")

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")

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")