G
Guest
I get "Subscript out of range" error when connecting with excel. The error
is to be intermitant and I can't determine a difinitive cause. I have
included my code to help.
I suspect if it is a problem in my code that it will be in the 'Cable
Detail' section since the error started occuring after I added this portion
of the code.
With objActiveWkb
On Error GoTo ErrHandle
.worksheets(1).Select
.worksheets(1).cells(10, 2) = Date
.worksheets(1).cells(13, 2) = Me.NodeCount
.worksheets(1).cells(14, 2) = Me.ActualBlocking
'***************Core Details***************
.worksheets(1).cells(19, 1) = Me.Cores_PartNum
.worksheets(1).cells(19, 2) = Me.Core & " - (STD) - (Core)"
.worksheets(1).cells(19, 3) = Me.CoreCount
.worksheets(1).cells(19, 4) = Me.CorePrice
.worksheets(1).cells(19, 5) = Me.CorePrice
'***************Leaf Details***************
.worksheets(1).cells(20, 1) = Me.Leafs_PartNum
.worksheets(1).cells(20, 2) = Me.Leaf & " - (STD) - (Leaf)"
.worksheets(1).cells(20, 4) = Me.LeafPrice
.worksheets(1).cells(20, 3) = Me.LeafCount
.worksheets(1).cells(20, 5) = Me.LeafPrice
'***************Ethernet Details***************
.worksheets(1).cells(21, 2) = Me.EthSwitch & " - (Ethernet)"
.worksheets(1).cells(21, 3) = Me.EthCnt
.worksheets(1).cells(21, 4) = Me.EthPrice
.worksheets(1).cells(21, 5) = Me.EthPrice
.worksheets(1).cells(21, 1) = Me.Ethernet_PartNum
'***************HCA Details***************
.worksheets(1).cells(22, 1) = Me.HCA_PartNum
.worksheets(1).cells(22, 2) = Me.HCAAll & " - (HCA)"
.worksheets(1).cells(22, 4) = Me.HCAPrice
.worksheets(1).cells(22, 5) = Me.HCAPrice
.worksheets(1).cells(22, 3) = Me.NodeCount
'***************Subnet Manager***************
If Me.SubNet = "SM Onboard" Then
.worksheets(1).cells(23, 2) = "Subnet Manager Onboard" & strHPerSub
.worksheets(1).cells(68, 4) = "Subnet Manager Onboard"
Else
.worksheets(1).cells(23, 1) = Me.SMPartNum
.worksheets(1).cells(23, 2) = Me.SubNet & " -(SubNet Manager)" &
strHPerSub
.worksheets(1).cells(23, 3) = 1
.worksheets(1).cells(23, 4) = Me.SubNetPrice
.worksheets(1).cells(23, 5) = Me.SubNetPrice
.worksheets(1).cells(76, 4) = Me.SubNet
End If
'***************Cabling Details***************
.worksheets(1).cells(24, 3) = CQtyTotal
.worksheets(1).cells(24, 6) = CCostTotal
.worksheets(1).cells(27, 4) = Me.InsCFDP
.worksheets(1).cells(28, 4) = Me.InsSRS
.worksheets(1).cells(29, 4) = Me.InsIBC
.worksheets(1).cells(30, 4) = Me.InsIBF
.worksheets(1).cells(31, 4) = EthManagePrice1
.worksheets(1).cells(32, 4) = Me.InsCCT
.worksheets(1).cells(33, 4) = Me.InsPM
.worksheets(1).cells(27, 5) = Me.InsCFDP2
.worksheets(1).cells(28, 5) = Me.InsSRS2
.worksheets(1).cells(29, 5) = Me.InsIBC2
.worksheets(1).cells(30, 5) = Me.InsIBF2
.worksheets(1).cells(31, 5) = EthManagePrice2
.worksheets(1).cells(32, 5) = Me.InsCCT2
.worksheets(1).cells(33, 5) = Me.InsPM2
.worksheets(1).cells(46, 5) = Me.NodeCount
.worksheets(1).cells(47, 5) = Me.MaxNodes
.worksheets(1).cells(48, 5) = Me.ActualBlocking
.worksheets(1).cells(49, 5) = Me.CorePortsPerLeaf * Me.LeafCount
.worksheets(1).cells(56, 5) = Me.LeafCount
.worksheets(1).cells(57, 5) = Me.LeafPorts
.worksheets(1).cells(58, 5) = Me.NodesPerLeaf
.worksheets(1).cells(59, 5) = Me.CorePortsPerLeaf
.worksheets(1).cells(60, 5) = Me.ISLtoCore
.worksheets(1).cells(55, 4) = Me.Leaf
.worksheets(1).cells(65, 4) = Me.Core
.worksheets(1).cells(66, 5) = Me.CoreCount
.worksheets(1).cells(67, 5) = Me.CorePorts
.worksheets(1).cells(37, 1) = strSMART & Me.Cores_PartNum
.worksheets(1).cells(38, 1) = strSMART & Me.Leafs_PartNum
.worksheets(1).cells(39, 1) = strSMART & Me.HCA_PartNum
.worksheets(1).cells(40, 1) = strSMART & Me.Ethernet_PartNum
.worksheets(1).cells(37, 4) = Me.CorePrice * Me.SmartPer
.worksheets(1).cells(38, 4) = Me.LeafPrice * Me.SmartPer
.worksheets(1).cells(39, 4) = Me.HCAPrice * Me.SmartPer
.worksheets(1).cells(40, 4) = Me.EthPrice * Me.SmartPer
.worksheets(1).cells(37, 3) = Me.CoreCount
.worksheets(1).cells(38, 3) = Me.LeafCount
.worksheets(1).cells(39, 3) = Me.NodeCount
.worksheets(1).cells(40, 3) = Me.EthCnt
.worksheets(1).cells(37, 2) = "Smartnet Support for Core Switches"
.worksheets(1).cells(38, 2) = "Smartnet Support for Leaf Switches"
.worksheets(1).cells(39, 2) = "Smartnet Support for Host Channel
Adapters"
.worksheets(1).cells(40, 2) = "Smartnet Support for Ethernet
Switches"
'*************** Expenses ***************
If Me.NodeCount > 4096 Then
.worksheets(1).cells(34, 2) = TEDescStr
.worksheets(1).cells(34, 5) = 0
.worksheets(1).cells(34, 4) = 0
.worksheets(1).cells(34, 6) = 0
Refresh
Else
.worksheets(1).cells(34, 2) = TEDescStr
If Me.TEDaysFab > Me.TEDaysLay Then
.worksheets(1).cells(34, 3) = Me.TEDaysFab
Else
.worksheets(1).cells(34, 3) = Me.TEDaysLay
End If
Refresh
.worksheets(1).cells(34, 5) = Me.TELayRes
.worksheets(1).cells(34, 4) = Me.TEFabRes
.worksheets(1).cells(34, 6) = Me.TECar + Me.TEFlight + Me.TEHotel
+ Me.TEMeals
'.worksheets(1).Cells(41, 7) = Me.TEFlight
'.worksheets(1).Cells(41, 8) = Me.TEHotel
'.worksheets(1).Cells(41, 9) = Me.TECar
'.worksheets(1).Cells(41, 10) = Me.TEMeals
End If
'*************** Cable Detail ***************
.worksheets(2).Select
Dim CabList As ListBox
Dim CabSel As Integer
Dim ExRow As Integer
Dim CableLength As Integer
Dim CableQuantity As Integer
Dim CablePrice As Currency
Set CabList = Me.CabDetList
For CabSel = 0 To CabList.ListCount - 1
ExRow = 5 + CabSel
If ExRow > 16 Then
GoTo ExitCableDetail
End If
Me.CabPnum = CabList.Column(0, CabSel)
Me.CabLength = CabList.Column(1, CabSel)
Me.CabCon = CabList.Column(2, CabSel)
Me.CabDesc = CabList.Column(3, CabSel)
Me.CabQty = CabList.Column(4, CabSel)
Me.CabPrice = CabList.Column(5, CabSel)
Refresh
CableLength = Me.CabLength
CableQuantity = Me.CabQty
CablePrice = Me.CabPrice
.worksheets(2).cells(ExRow, 1) = Me.CabPnum
.worksheets(2).cells(ExRow, 2) = CableLength
.worksheets(2).cells(ExRow, 3) = Me.CabCon
.worksheets(2).cells(ExRow, 4) = Me.CabDesc
.worksheets(2).cells(ExRow, 5) = CableQuantity
.worksheets(2).cells(ExRow, 6) = CablePrice
Next CabSel
ExitCableDetail:
'*************** Topology ***************
.worksheets(1).Select
If Me.NodeCount > 1152 Then
.worksheets(1).cells(70, 4) = "Cisco High Performance Recommended"
Else
.worksheets(1).cells(70, 4) = ""
End If
.worksheets(1).cells(43, 1).Select
.worksheets(1).Pictures.Insert(CurrentProject.Path &
"\CTemp\TempBMP.bmp").Select
objXL.Selection.Name = "Topology"
objXL.Selection.ShapeRange.IncrementLeft 110#
objXL.Selection.ShapeRange.IncrementTop 0.75
.worksheets(1).cells(15, 6).Select
End With
fs.DeleteFile (CurrentProject.Path & "\CTemp\TempBMP.bmp")
Dim strCoreDet As String
Dim strLeafDet As String
Dim strEthDet As String
strCoreDet = Me.Cores_PartNum & "-" & Me.CorePorts
strLeafDet = Me.Leafs_PartNum & "-" & Me.LeafPorts
If IsNull(Me.EthID) Then
Else
strEthDet = Me.Ethernet_PartNum
End If
If fs.FileExists(CurrentProject.Path & "\CTemp\CSwDetails.xls") = True
Then
strInputFileName2 = CurrentProject.Path & "\CTemp\CSwDetails.xls"
objXL.Application.Workbooks.Open (strInputFileName2)
Set objActiveWkb2 = objXL.Application.ActiveWorkBook
objActiveWkb2.sheets(strCoreDet).Select
objActiveWkb2.sheets(strCoreDet).copy After:=objActiveWkb.sheets(1)
objActiveWkb.sheets(2).Name = "Core Detail"
If Me.LeafCount > 0 Then
objActiveWkb2.sheets(strLeafDet).copy After:=objActiveWkb.sheets(2)
objActiveWkb.sheets(3).Name = "Leaf Detail"
End If
If IsNull(Me.EthID) Then
Else
If Me.LeafCount > 0 Then
objActiveWkb2.sheets(strEthDet).copy After:=objActiveWkb.sheets(3)
objActiveWkb.sheets(4).Name = "Ethernet Detail"
Else
objActiveWkb2.sheets(strEthDet).copy After:=objActiveWkb.sheets(2)
objActiveWkb.sheets(3).Name = "Ethernet Detail"
End If
End If
objActiveWkb.sheets(1).Select
objActiveWkb2.Close savechanges:=False
End If
objActiveWkb.Close savechanges:=True
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
Thanks for taking a look and any help you can give!
Tim J
is to be intermitant and I can't determine a difinitive cause. I have
included my code to help.
I suspect if it is a problem in my code that it will be in the 'Cable
Detail' section since the error started occuring after I added this portion
of the code.
With objActiveWkb
On Error GoTo ErrHandle
.worksheets(1).Select
.worksheets(1).cells(10, 2) = Date
.worksheets(1).cells(13, 2) = Me.NodeCount
.worksheets(1).cells(14, 2) = Me.ActualBlocking
'***************Core Details***************
.worksheets(1).cells(19, 1) = Me.Cores_PartNum
.worksheets(1).cells(19, 2) = Me.Core & " - (STD) - (Core)"
.worksheets(1).cells(19, 3) = Me.CoreCount
.worksheets(1).cells(19, 4) = Me.CorePrice
.worksheets(1).cells(19, 5) = Me.CorePrice
'***************Leaf Details***************
.worksheets(1).cells(20, 1) = Me.Leafs_PartNum
.worksheets(1).cells(20, 2) = Me.Leaf & " - (STD) - (Leaf)"
.worksheets(1).cells(20, 4) = Me.LeafPrice
.worksheets(1).cells(20, 3) = Me.LeafCount
.worksheets(1).cells(20, 5) = Me.LeafPrice
'***************Ethernet Details***************
.worksheets(1).cells(21, 2) = Me.EthSwitch & " - (Ethernet)"
.worksheets(1).cells(21, 3) = Me.EthCnt
.worksheets(1).cells(21, 4) = Me.EthPrice
.worksheets(1).cells(21, 5) = Me.EthPrice
.worksheets(1).cells(21, 1) = Me.Ethernet_PartNum
'***************HCA Details***************
.worksheets(1).cells(22, 1) = Me.HCA_PartNum
.worksheets(1).cells(22, 2) = Me.HCAAll & " - (HCA)"
.worksheets(1).cells(22, 4) = Me.HCAPrice
.worksheets(1).cells(22, 5) = Me.HCAPrice
.worksheets(1).cells(22, 3) = Me.NodeCount
'***************Subnet Manager***************
If Me.SubNet = "SM Onboard" Then
.worksheets(1).cells(23, 2) = "Subnet Manager Onboard" & strHPerSub
.worksheets(1).cells(68, 4) = "Subnet Manager Onboard"
Else
.worksheets(1).cells(23, 1) = Me.SMPartNum
.worksheets(1).cells(23, 2) = Me.SubNet & " -(SubNet Manager)" &
strHPerSub
.worksheets(1).cells(23, 3) = 1
.worksheets(1).cells(23, 4) = Me.SubNetPrice
.worksheets(1).cells(23, 5) = Me.SubNetPrice
.worksheets(1).cells(76, 4) = Me.SubNet
End If
'***************Cabling Details***************
.worksheets(1).cells(24, 3) = CQtyTotal
.worksheets(1).cells(24, 6) = CCostTotal
.worksheets(1).cells(27, 4) = Me.InsCFDP
.worksheets(1).cells(28, 4) = Me.InsSRS
.worksheets(1).cells(29, 4) = Me.InsIBC
.worksheets(1).cells(30, 4) = Me.InsIBF
.worksheets(1).cells(31, 4) = EthManagePrice1
.worksheets(1).cells(32, 4) = Me.InsCCT
.worksheets(1).cells(33, 4) = Me.InsPM
.worksheets(1).cells(27, 5) = Me.InsCFDP2
.worksheets(1).cells(28, 5) = Me.InsSRS2
.worksheets(1).cells(29, 5) = Me.InsIBC2
.worksheets(1).cells(30, 5) = Me.InsIBF2
.worksheets(1).cells(31, 5) = EthManagePrice2
.worksheets(1).cells(32, 5) = Me.InsCCT2
.worksheets(1).cells(33, 5) = Me.InsPM2
.worksheets(1).cells(46, 5) = Me.NodeCount
.worksheets(1).cells(47, 5) = Me.MaxNodes
.worksheets(1).cells(48, 5) = Me.ActualBlocking
.worksheets(1).cells(49, 5) = Me.CorePortsPerLeaf * Me.LeafCount
.worksheets(1).cells(56, 5) = Me.LeafCount
.worksheets(1).cells(57, 5) = Me.LeafPorts
.worksheets(1).cells(58, 5) = Me.NodesPerLeaf
.worksheets(1).cells(59, 5) = Me.CorePortsPerLeaf
.worksheets(1).cells(60, 5) = Me.ISLtoCore
.worksheets(1).cells(55, 4) = Me.Leaf
.worksheets(1).cells(65, 4) = Me.Core
.worksheets(1).cells(66, 5) = Me.CoreCount
.worksheets(1).cells(67, 5) = Me.CorePorts
.worksheets(1).cells(37, 1) = strSMART & Me.Cores_PartNum
.worksheets(1).cells(38, 1) = strSMART & Me.Leafs_PartNum
.worksheets(1).cells(39, 1) = strSMART & Me.HCA_PartNum
.worksheets(1).cells(40, 1) = strSMART & Me.Ethernet_PartNum
.worksheets(1).cells(37, 4) = Me.CorePrice * Me.SmartPer
.worksheets(1).cells(38, 4) = Me.LeafPrice * Me.SmartPer
.worksheets(1).cells(39, 4) = Me.HCAPrice * Me.SmartPer
.worksheets(1).cells(40, 4) = Me.EthPrice * Me.SmartPer
.worksheets(1).cells(37, 3) = Me.CoreCount
.worksheets(1).cells(38, 3) = Me.LeafCount
.worksheets(1).cells(39, 3) = Me.NodeCount
.worksheets(1).cells(40, 3) = Me.EthCnt
.worksheets(1).cells(37, 2) = "Smartnet Support for Core Switches"
.worksheets(1).cells(38, 2) = "Smartnet Support for Leaf Switches"
.worksheets(1).cells(39, 2) = "Smartnet Support for Host Channel
Adapters"
.worksheets(1).cells(40, 2) = "Smartnet Support for Ethernet
Switches"
'*************** Expenses ***************
If Me.NodeCount > 4096 Then
.worksheets(1).cells(34, 2) = TEDescStr
.worksheets(1).cells(34, 5) = 0
.worksheets(1).cells(34, 4) = 0
.worksheets(1).cells(34, 6) = 0
Refresh
Else
.worksheets(1).cells(34, 2) = TEDescStr
If Me.TEDaysFab > Me.TEDaysLay Then
.worksheets(1).cells(34, 3) = Me.TEDaysFab
Else
.worksheets(1).cells(34, 3) = Me.TEDaysLay
End If
Refresh
.worksheets(1).cells(34, 5) = Me.TELayRes
.worksheets(1).cells(34, 4) = Me.TEFabRes
.worksheets(1).cells(34, 6) = Me.TECar + Me.TEFlight + Me.TEHotel
+ Me.TEMeals
'.worksheets(1).Cells(41, 7) = Me.TEFlight
'.worksheets(1).Cells(41, 8) = Me.TEHotel
'.worksheets(1).Cells(41, 9) = Me.TECar
'.worksheets(1).Cells(41, 10) = Me.TEMeals
End If
'*************** Cable Detail ***************
.worksheets(2).Select
Dim CabList As ListBox
Dim CabSel As Integer
Dim ExRow As Integer
Dim CableLength As Integer
Dim CableQuantity As Integer
Dim CablePrice As Currency
Set CabList = Me.CabDetList
For CabSel = 0 To CabList.ListCount - 1
ExRow = 5 + CabSel
If ExRow > 16 Then
GoTo ExitCableDetail
End If
Me.CabPnum = CabList.Column(0, CabSel)
Me.CabLength = CabList.Column(1, CabSel)
Me.CabCon = CabList.Column(2, CabSel)
Me.CabDesc = CabList.Column(3, CabSel)
Me.CabQty = CabList.Column(4, CabSel)
Me.CabPrice = CabList.Column(5, CabSel)
Refresh
CableLength = Me.CabLength
CableQuantity = Me.CabQty
CablePrice = Me.CabPrice
.worksheets(2).cells(ExRow, 1) = Me.CabPnum
.worksheets(2).cells(ExRow, 2) = CableLength
.worksheets(2).cells(ExRow, 3) = Me.CabCon
.worksheets(2).cells(ExRow, 4) = Me.CabDesc
.worksheets(2).cells(ExRow, 5) = CableQuantity
.worksheets(2).cells(ExRow, 6) = CablePrice
Next CabSel
ExitCableDetail:
'*************** Topology ***************
.worksheets(1).Select
If Me.NodeCount > 1152 Then
.worksheets(1).cells(70, 4) = "Cisco High Performance Recommended"
Else
.worksheets(1).cells(70, 4) = ""
End If
.worksheets(1).cells(43, 1).Select
.worksheets(1).Pictures.Insert(CurrentProject.Path &
"\CTemp\TempBMP.bmp").Select
objXL.Selection.Name = "Topology"
objXL.Selection.ShapeRange.IncrementLeft 110#
objXL.Selection.ShapeRange.IncrementTop 0.75
.worksheets(1).cells(15, 6).Select
End With
fs.DeleteFile (CurrentProject.Path & "\CTemp\TempBMP.bmp")
Dim strCoreDet As String
Dim strLeafDet As String
Dim strEthDet As String
strCoreDet = Me.Cores_PartNum & "-" & Me.CorePorts
strLeafDet = Me.Leafs_PartNum & "-" & Me.LeafPorts
If IsNull(Me.EthID) Then
Else
strEthDet = Me.Ethernet_PartNum
End If
If fs.FileExists(CurrentProject.Path & "\CTemp\CSwDetails.xls") = True
Then
strInputFileName2 = CurrentProject.Path & "\CTemp\CSwDetails.xls"
objXL.Application.Workbooks.Open (strInputFileName2)
Set objActiveWkb2 = objXL.Application.ActiveWorkBook
objActiveWkb2.sheets(strCoreDet).Select
objActiveWkb2.sheets(strCoreDet).copy After:=objActiveWkb.sheets(1)
objActiveWkb.sheets(2).Name = "Core Detail"
If Me.LeafCount > 0 Then
objActiveWkb2.sheets(strLeafDet).copy After:=objActiveWkb.sheets(2)
objActiveWkb.sheets(3).Name = "Leaf Detail"
End If
If IsNull(Me.EthID) Then
Else
If Me.LeafCount > 0 Then
objActiveWkb2.sheets(strEthDet).copy After:=objActiveWkb.sheets(3)
objActiveWkb.sheets(4).Name = "Ethernet Detail"
Else
objActiveWkb2.sheets(strEthDet).copy After:=objActiveWkb.sheets(2)
objActiveWkb.sheets(3).Name = "Ethernet Detail"
End If
End If
objActiveWkb.sheets(1).Select
objActiveWkb2.Close savechanges:=False
End If
objActiveWkb.Close savechanges:=True
If boolXL Then objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing
Thanks for taking a look and any help you can give!
Tim J