TA的每日心情 | 奋斗 昨天 09:34 |
---|
签到天数: 815 天 [LV.10]以坛为家III
|
楼主 |
发表于 2022-4-2 00:18:33
|
显示全部楼层
Public cnn As New ADODB.Connection
Public cnn2 As New ADODB.Connection
Public qy1 As New ADODB.Recordset
Public qy2 As New ADODB.Recordset
Public qy3 As New ADODB.Recordset
Public qy4 As New ADODB.Recordset
Dim NewApp As New Excel.Application
Dim NewSheet As New Excel.Worksheet
Dim NewBook As New Excel.Workbook
Dim x As Long, y As Long, z As Long
Dim n(1 To 12) As String
Private Sub Command2_Click()
Timer1.Enabled = False
NewBook.Close
NewApp.Quit
End Sub
Private Sub Command3_Click()
CommonDialog1.Filter = "Excel File|*.xls"
CommonDialog1.ShowOpen
If CommonDialog1.FileName <> "" Then Text1.Text = CommonDialog1.FileName
End Sub
Private Sub Command4_Click()
If Text1.Text = "" Then Exit Sub
Set NewApp = New Excel.Application
Set NewBook = NewApp.Workbooks.Open(Text1.Text)
'第一位为路径,第五位为密码
Set NewSheet = NewBook.Worksheets(1)
If NewSheet.Cells(1, 1) = "编号" And NewSheet.Cells(1, 2) = "货品名称" And NewSheet.Cells(1, 3) = "货品全名" And _
NewSheet.Cells(1, 4) = "基本单位" And NewSheet.Cells(1, 5) = "规格" And NewSheet.Cells(1, 6) = "条码" And _
NewSheet.Cells(1, 7) = "商品类型" And NewSheet.Cells(1, 8) = "主供应商" Then
Timer1.Enabled = True
Else
MsgBox "Excel 文档格式不合要求", vbInformation, "注意"
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
End
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Timer1_Timer()
For i = 1 To 12
n(i) = NewSheet.Cells(2 + y, i) '获取的数据
Next i
If n(1) = "" And n(2) = "" And n(3) = "" And n(4) = "" And n(5) = "" And n(6) = "" And n(7) = "" And n(8) = "" Then
Timer1.Enabled = False
MsgBox " 共增加了 " & y & " 个货品,操作完成!", vbInformation, "注意"
x = 0: y = 0: z = 0
NewBook.Close
NewApp.Quit
Exit Sub
End If
Set qy1 = cnn.Execute("select * from B_Goods where G_BarCode='" & n(6) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Else
x = x + 1
GoTo Gnext
End If
If n(1) = "" Then '处理空编码
Set qy1 = cnn.Execute("select * from B_Goods order by log(G_ID) DESC")
n(1) = Str(Val(qy1.Fields(1)) + 1)
End If
If n(2) = "" Or n(3) = "" Or n(4) = "" Or n(6) = "" Or n(7) = "" Or n(8) = "" Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行数据不完整或者无数据", vbInformation, "注意"
End If
Set qy1 = cnn.Execute("select * from B_Unit where U_Name='" & n(4) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行,数据库中没有找到“" & n(4) & "”这个基本单位。", vbInformation, "注意"
End If
Set qy1 = cnn.Execute("select * from B_GoodsType where Name='" & n(7) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行,数据库中没有找到“" & n(7) & "”这个商品类型。", vbInformation, "注意"
Else
n(7) = qy1.Fields(1)
End If
Set qy1 = cnn.Execute("select * from B_Vendor where V_Name='" & n(8) & "'")
If qy1.EOF = True And qy1.BOF = True Then
Timer1.Enabled = False
MsgBox (2 + y) & " 行,数据库中没有找到“" & n(8) & "”这个供应商。", vbInformation, "注意"
Else
n(8) = qy1.Fields(1)
End If
If Timer1.Enabled = True Then
Set qy1 = cnn.Execute("insert into B_Goods(G_ID,G_PinYin,G_Name,G_FullName,G_BarCode,TypeID,PriceType,G_Unit,G_UnitRate,V_ID,Cal_ID,S_ID,G_PArea,G_Manufacturer,G_Price,G_LastPrice,G_SalePrice,V_Price,isBZQ,RateNum)values('" & _
n(1) & "','" & "" & "','" & n(2) & "','" & n(3) & "','" & n(6) & "','" & n(7) & "','" & "111" & "','" & n(4) & "','" & n(5) & "','" & n(8) & "','" & 0 & "','" & "0001" & "','" & "" & "','" & "" & "'," & n(9) & "," & n(10) & "," & n(11) & "," & n(12) & ",'" & 0 & "'," & 0 & ")")
z = z + 1
End If
Gnext:
y = y + 1
Label1.Caption = "共读取商品数 " & y & " 个,其中增加商品数 " & z & " 个,重复商品数 " & x & " 个!(将不会增加到商品库中)"
End Sub
|
|