1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85 | Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
Dim wbk As Object
wbk = oExcel.Workbooks.Open(Filename:="C:\Users\Dev1\Downloads\11-21-2011\Quest Technology Report - InVoice Account Daily_13-12-01-2011.xls", UpdateLinks:=False, ReadOnly:=False)
Dim firstletter As Integer = 65 '65 =A 90 Z
Dim secondletter As Integer = 0
' "Zipp Technologies LTD" last
objConn.open()
'For i As Integer = 5089 To 5937
For i As Integer = 4 To 102
With wbk.ActiveSheet
'For j = 2 To 5
firstletter = firstletter + 1
'If Len((.Range(Chr(firstletter) & "1").value)) > 0 Then
ListBox1.Items.Add(.Cells(i, 2).Value & "-" & formatDate(.Cells(i, 3).Value))
Dim CountSQl As String = "select * from invoice where accountno = '" & RTrim(.Cells(i, 2).Value) & "' and dateissue = '" & formatDate(.Cells(i, 3).Value) & "' and productcode = '" & .Cells(i, 9).Value & "'"
Dim cmd As OdbcCommand = New OdbcCommand(CountSQl, objConn)
Dim reader As OdbcDataReader
reader = cmd.ExecuteReader()
If reader.Read() Then
txtAccountNo.Text &= (.Cells(i, 2).Value)
'insertInvoice(.Cells(i, 2).Value, formatDate(.Cells(i, 3).Value), .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 11).Value)
Else
insertInvoice(RTrim(.Cells(i, 2).Value), formatDate(.Cells(i, 3).Value), .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 11).Value)
End If
reader.Close()
'insertSimorder(.Cells(i, 1).Value, .Cells(i, 2).Value, .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 11).Value, .Cells(i, 12).Value, .Cells(i, 13).Value, .Cells(i, 14).Value, "0000-00-00", .Cells(i, 16).Value, formatDate(.Cells(i, 17).Value), .Cells(i, 18).Value, .Cells(i, 19).Value)
'updateAcctMgr(.Range(Chr(firstletter) & "29").value(), searchClientID(.Range(Chr(firstletter) & "1").value()).Tables(0).Rows(0).Item("carrierDetailsID"))
'insertSimrequest(.Cells(i, 2).Value, .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 13).Value, formatDate(.Cells(i, 19).Value), formatDate(.Cells(i, 21).Value), .Cells(i, 22).Value, .Cells(i, 23).Value, .Cells(i, 14).Value)
'insertSimrequest(.Cells(i, 2).Value, .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 13).Value, formatDate(.Cells(i, 19).Value), formatDate(.Cells(i, 21).Value), .Cells(i, 22).Value, .Cells(i, 23).Value, .Cells(i, 15).Value)
'insertSimrequest(.Cells(i, 2).Value, .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, .Cells(i, 7).Value, .Cells(i, 8).Value, .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 13).Value, formatDate(.Cells(i, 19).Value), formatDate(.Cells(i, 21).Value), .Cells(i, 22).Value, .Cells(i, 23).Value, .Cells(i, 16).Value)
'End If
If firstletter = 90 Then
firstletter = 65
If secondletter = 0 Then secondletter = 65
If secondletter > 0 Then
secondletter = secondletter + 1
End If
End If
'Next
End With
Next
oExcel.Workbooks.close()
objConn.close()
MsgBox("done")
End Sub
|
