初步了解vba。利用excel自动生成我想要的insert语句

Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
Sub zidong()

cellNum = Sheet1.[a65536].End(xlUp).Row

For i = 2 To cellNum
'开始创建货品===================================================
'每次生成唯一id
tt = Timer
h = Int(tt / 3600)
m = Int((tt - 3600 * h) / 60)
s = Int(tt - h * 3600 - m * 60)
ss = Left(tt - Int(tt), 15)
ss = Replace(ss, ".", "")
ss = ss + "000"
id = Format(Date, "yyyymmdd") + Format(Time, "hhmmss") + Left(ss, 7) + Left(CreateGUID, 11)
'每次获取创建时间
createtime = Format(Date, "yyyy-mm-dd") + Format(Time, " hh:mm:ss")
'开始写insert语句
insertdate = "INSERT INTO PaasOLT_Goods VALUES("
'拼接id
insertdate = insertdate + "'" + id + "',"
'拼接时间
insertdate = insertdate + "'" + createtime + "',"
'修改时间同创建时间
insertdate = insertdate + "'" + createtime + "',"
'创建人同修改人
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "" + "0" + ","
insertdate = insertdate + "'" + "" + "',"
'GoodsName
insertdate = insertdate + "'" + Sheet1.Cells(i, 1) + "',"
'GoodsPrice
insertdate = insertdate + "" + Str(Sheet1.Cells(i, 2)) + ","
'GoodsStock
insertdate = insertdate + "" + Str(Sheet1.Cells(i, 3)) + ","
'GoodsSpecification
insertdate = insertdate + "'" + Sheet1.Cells(i, 4) + "',"
'GoodsPhoto
insertdate = insertdate + "'" + "" + "',"
'GoodsDescription
insertdate = insertdate + "'" + Sheet1.Cells(i, 5) + "',"
'EnumGoodsType
insertdate = insertdate + "'" + "1" + "',"
'GoodsNum
insertdate = insertdate + "" + "NULL" + ","
'PK_PaasOLT_Warehouse_WarehouseName
insertdate = insertdate + "" + "NULL" + ","
'SalesPrice
insertdate = insertdate + "" + "NULL" + ","
'PK_PaasOLT_CommodityCategory_Name
insertdate = insertdate + "" + "NULL" + ","
'Remark
insertdate = insertdate + "" + "NULL" + ","
'GoodsUnit
insertdate = insertdate + "" + "NULL" + ","
'EnumGoodsSupplier
insertdate = insertdate + "" + "NULL"
'结束insert
insertdate = insertdate + ")" & Chr(10) & ""
'创建货品结束=========================================================================
Sheet2.Cells(i, 1) = insertdate
'创建商品开始=========================================================================
'每次生成唯一id
tt = Timer
h = Int(tt / 3600)
m = Int((tt - 3600 * h) / 60)
s = Int(tt - h * 3600 - m * 60)
ss = Left(tt - Int(tt), 15)
ss = Replace(ss, ".", "")
ss = ss + "000"
Commodityid = Format(Date, "yyyymmdd") + Format(Time, "hhmmss") + Left(ss, 7) + Left(CreateGUID, 11)
'每次获取创建时间
createtime = Format(Date, "yyyy-mm-dd") + Format(Time, " hh:mm:ss")
'开始写insert语句
insertdate = "INSERT INTO PaasOLT_Commodity VALUES("
'拼接id
insertdate = insertdate + "'" + Commodityid + "',"
'拼接时间
insertdate = insertdate + "'" + createtime + "',"
'修改时间同创建时间
insertdate = insertdate + "'" + createtime + "',"
'创建人同修改人
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "" + "0" + ","
insertdate = insertdate + "'" + "" + "',"
'CommodityName
insertdate = insertdate + "'" + Sheet1.Cells(i, 6) + "',"
'PK_PaasOLT_CommodityCategory_Name
insertdate = insertdate + "'" + Sheet1.Cells(i, 8) + "',"
'CommodityCode
insertdate = insertdate + "" + "NULL" + ","
'CommodityKeyWord
insertdate = insertdate + "" + "NULL" + ","
'CommodityDescription
insertdate = insertdate + "" + "NULL" + ","
'CommodityPhoto
insertdate = insertdate + "" + "NULL" + ","
'CommodityMoney
insertdate = insertdate + "" + Str(Sheet1.Cells(i, 7)) + ","
'CommoditySalesVolume
insertdate = insertdate + "" + "0" + ","
'CommodityStock
insertdate = insertdate + "" + Str(Sheet1.Cells(i, 3)) + ","
'EnumCommodityStatus
insertdate = insertdate + "'" + "10" + "',"
'SwitchBtnJoinDiscount
insertdate = insertdate + "'" + "1" + "',"
'CommodityTag
insertdate = insertdate + "" + "NULL" + ","
'EnumCommodityType
insertdate = insertdate + "'" + "1" + "',"
'PK_PaasOLT_CarType_Name
insertdate = insertdate + "" + "NULL" + ","
'PreBookingDay
insertdate = insertdate + "" + "0" + ","
'Remark
insertdate = insertdate + "'" + Sheet1.Cells(i, 9) + "'"
'结束insert
insertdate = insertdate + ")" & Chr(10) & ""
'创建商品结束=========================================================================
Sheet2.Cells(i, 2) = insertdate
'创建商品货品关系开始=================================================================
'每次生成唯一id
tt = Timer
h = Int(tt / 3600)
m = Int((tt - 3600 * h) / 60)
s = Int(tt - h * 3600 - m * 60)
ss = Left(tt - Int(tt), 15)
ss = Replace(ss, ".", "")
ss = ss + "000"
CommodityGoodsid = Format(Date, "yyyymmdd") + Format(Time, "hhmmss") + Left(ss, 7) + Left(CreateGUID, 11)
'每次获取创建时间
createtime = Format(Date, "yyyy-mm-dd") + Format(Time, " hh:mm:ss")
'开始写insert语句
insertdate = "INSERT INTO PaasOLT_CommodityGoods VALUES("
'拼接id
insertdate = insertdate + "'" + CommodityGoodsid + "',"
'拼接时间
insertdate = insertdate + "'" + createtime + "',"
'修改时间同创建时间
insertdate = insertdate + "'" + createtime + "',"
'创建人同修改人
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "'" + "SYSTEM" + "',"
insertdate = insertdate + "" + "0" + ","
insertdate = insertdate + "'" + "" + "',"
'商品主键
insertdate = insertdate + "'" + Commodityid + "',"
'货品主键
insertdate = insertdate + "'" + id + "',"
'货品数量
insertdate = insertdate + "" + "1"
'结束insert
insertdate = insertdate + ")" & Chr(10) & ""
'创建商品结束=========================================================================
Sheet2.Cells(i, 3) = insertdate
Next


MsgBox 123
End Sub

Private Function CreateGUID() As String

    Dim id(0 To 15) As Byte
      
    Dim Cnt As Long, GUID As String
      
    If CoCreateGuid(id(0)) = 0 Then
          
        For Cnt = 0 To 15
            CreateGUID = CreateGUID + IIf(id(Cnt) < 16, "0", "") + LCase(Hex$(id(Cnt)))
        Next Cnt
          
        CreateGUID = Left$(CreateGUID, 8) + Mid$(CreateGUID, 9, 4) + Mid$(CreateGUID, 13, 4) + Mid$(CreateGUID, 17, 4) + Right$(CreateGUID, 12)
      
    Else
          
        MsgBox "Error while creating GUID!"
          
    End If
      
End Function

 

效果图

第一次用宏。还挺不错

 

——————————————————————————————————

作者：Henny_CHN

转载请标明出处，原文地址：  

https://blog.csdn.net/a1234012340a/article/details/101384650

如果感觉本文对您有帮助，请留下您的赞，您的支持是我坚持写作最大的动力，谢谢！