abenitez77
Board Regular
- Joined
- Dec 30, 2004
- Messages
- 149
How do I put this all into an array ? I later have to use these variables to insert into a sql table.
Code:
strBanner = ""
strBannerCode = ""
strBanner2 = ""
strBannerCode2 = ""
strBanner3 = ""
strBannerCode3 = ""
strBanner4 = ""
strBannerCode4 = ""
strBanner5 = ""
strBannerCode5 = ""
strBanner6 = ""
strBannerCode6 = ""
strBanner7 = ""
strBannerCode7 = ""
strBanner8 = ""
strBannerCode8 = ""
strBanner9 = ""
strBannerCode9 = ""
strBanner10 = ""
strBannerCode10 = ""
'Search for "X" markers **************************************************************************************
mySearch5 = "X"
Set iSheetE = Range("A44:O54").Find(What:=mySearch5, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not iSheetE Is Nothing Then
strBanner = Trim(iSheetE.Offset(0, 1).Text)
strBannerCode = Trim(iSheetE.Offset(0, 2).Text)
MyAddr = iSheetE.Address
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE2 = .FindNext(iSheetE)
If Not iSheetE2 Is Nothing And MyAddr <> iSheetE2.Address Then
strBanner2 = Trim(iSheetE2.Offset(0, 1).Text)
strBannerCode2 = Trim(iSheetE2.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE3 = .FindNext(iSheetE2)
If Not iSheetE3 Is Nothing And MyAddr <> iSheetE3.Address Then
strBanner3 = Trim(iSheetE3.Offset(0, 1).Text)
strBannerCode3 = Trim(iSheetE3.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE4 = .FindNext(iSheetE3)
If Not iSheetE4 Is Nothing And MyAddr <> iSheetE4.Address Then
strBanner4 = Trim(iSheetE4.Offset(0, 1).Text)
strBannerCode4 = Trim(iSheetE.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE5 = .FindNext(iSheetE4)
If Not iSheetE5 Is Nothing And MyAddr <> iSheetE5.Address Then
strBanner5 = Trim(iSheetE5.Offset(0, 1).Text)
strBannerCode5 = Trim(iSheetE5.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE6 = .FindNext(iSheetE5)
If Not iSheetE6 Is Nothing And MyAddr <> iSheetE6.Address Then
strBanner6 = Trim(iSheetE6.Offset(0, 1).Text)
strBannerCode6 = Trim(iSheetE6.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE7 = .FindNext(iSheetE6)
If Not iSheetE7 Is Nothing And MyAddr <> iSheetE7.Address Then
strBanner7 = Trim(iSheetE7.Offset(0, 1).Text)
strBannerCode7 = Trim(iSheetE7.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE8 = .FindNext(iSheetE7)
If Not iSheetE8 Is Nothing And MyAddr <> iSheetE8.Address Then
strBanner8 = Trim(iSheetE8.Offset(0, 1).Text)
strBannerCode8 = Trim(iSheetE8.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE9 = .FindNext(iSheetE8)
If Not iSheetE9 Is Nothing And MyAddr <> iSheetE9.Address Then
strBanner9 = Trim(iSheetE9.Offset(0, 1).Text)
strBannerCode9 = Trim(iSheetE9.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
'Search Next X
Set iSheetE10 = .FindNext(iSheetE9)
If Not iSheetE10 Is Nothing And MyAddr <> iSheetE10.Address Then
strBanner10 = Trim(iSheetE10.Offset(0, 1).Text)
strBannerCode10 = Trim(iSheetE10.Offset(0, 2).Text)
Else
GoTo NoMoreX
End If
NoMoreX:
' --------------------------- Get HDR Values from Cells -------------------------------------------
' ***************************** Insert Header info into XlsHdr Table ********************************
tSQL = "Insert Into XLSHdr (DealNum, VendDealNum, DateCreated, DealDate, DealType, ActivityType, DeliveryMethod, BillType, CatNum, PCSNum, MCH1, MCH1Desc," & _
"Week, YearNum, DealDuration, PreShipEffDate, DealEffDate, Vendor, VendorNum, SAPVendorNum," & _
"ManAddress, ManCity, ManProvince, ManPostalCode, ManPhone, ManRepName, ManCategory," & _
"BrokerName, BrokerAddress, BrokerCity, BrokerProvince, BrokerPostalCode, BrokerPhone, BrokerRepName, BrokerCategory, Banner, BannerCode," & _
"Banner2, BannerCode2, Banner3, BannerCode3, Banner4, BannerCode4, Banner5, BannerCode5, Banner6, BannerCode6," & _
"Banner7, BannerCode7, Banner8, BannerCode8, Banner9, BannerCode9, Banner10, BannerCode10, TabName, FileHyperLink, FileName, DocumentType) "
tSQL = tSQL & "Values('" & strLCLDeal & "','" & strVendorsDeal & "','" & CStr(strDateCreated) & "','" & strDealDate & "','" & strDealType & "','" & strActivityType & "','" & _
strDeliveryMethod & "','" & strBillType & "','" & strCatNum & "','" & strPCSNum & "','" & strMCH1 & "','" & strMch1Desc & "','" & strWeek & "','" & _
strYear & "','" & strDealDur & "','" & strPreShipEffDate & "','" & strDealEffDate & "','" & _
strManVendorName & "','" & strManVendorNum & "','" & strManSAPVendNum & "','" & strManAddress & "','" & strManCity & "','" & strManProvince & "','" & strManPostal & _
"','" & strManPhone & "','" & strManRepName & "','" & strManCategory & "','" & strBrokerName & "','" & strBrokerStreet & "','" & strBrokerCity & "','" & _
strBrokerProvince & "','" & strBrokerPostal & "','" & strBrokerPhone & "','" & strBrokerRepName & "','" & strBrokerCategory & "','" & strBanner & "','" & _
strBannerCode & "','" & strBanner2 & "','" & strBannerCode2 & "','" & strBanner3 & "','" & strBannerCode3 & "','" & strBanner4 & "','" & strBannerCode4 & _
"','" & strBanner5 & "','" & strBannerCode5 & "','" & strBanner6 & "','" & strBannerCode6 & "','" & strBanner7 & "','" & strBannerCode7 & "','" & _
strBanner8 & "','" & strBannerCode8 & "','" & strBanner9 & "','" & strBannerCode9 & "','" & strBanner10 & "','" & strBannerCode10 & _
"','" & xSheetName & "','" & xFilePath & "','" & xFileName & "','" & strXLSType & "')"
Set rs = Connection2.Execute(tSQL)
Last edited: