vba批量导⼊mysql_VBA将Excel数据导⼊到数据库学步园1、如果Excel中的数据是标志格式的，即标题栏+数据这种类型，那么导⼊数据库将⾮常⽅便，⽰例代码如下：
'函数：导⼊
Private Function F_K_Import() As Boolean
Dim cnCurrent As ADODB.Connection
Dim rcdTemp As ADODB.Recordset
Dim rcdChecker As ADODB.Recordset
Dim strSql As String
Dim ExcelApp
Dim ExcelWorkBook
Dim strWorkSheetName As String
On Error GoTo ErrHandle
F_K_Import= False
DoCmd.SetWarnings False
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
Set ExcelWorkBook = ExcelApp.WorkBooks.Open(_Import.Value))
strWorkSheetName = ExcelWorkBook.WorkSheets(1).Name
ExcelWorkBook.Close
Set ExcelWorkBook = Nothing
Set ExcelApp = Nothing
strSql = "SELECT * INTO [T_K] FROM [Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & _Import.Value) & "].[" & strWorkSheetName & "$]"
mysql下载appDoCmd.RunSQL strSql
DoCmd.SetWarnings True
F_K_Import= True
On Error GoTo 0
Exit Function
ErrHandle:
DoCmd.SetWarnings True
MsgBox Error(Err), vbExclamation
End Function
2、导⼊⾮标准格式的Excel时，基本⽅法是使⽤循环去读取Excel中的数据，⽰例代码如下：
Do While (strTradeNo <> "")
strTradeDate = ExcelWorkSheet.Cells(intRow, 2).Value
strProductVariety = ExcelWorkSheet.Cells(intRow, 3).Value
strValueDate = ExcelWorkSheet.Cells(intRow, 4).Value
strFixedRatePayer = ExcelWorkSheet.Cells(intRow, 5).Value
strFixedRate = ExcelWorkSheet.Cells(intRow, 6).Value
strFloatRatePayer = ExcelWorkSheet.Cells(intRow, 7).Value
strBPs = ExcelWorkSheet.Cells(intRow, 8).Value
strSql = "INSERT INTO [T_LiquidationNotice]
(Ccy,CustomerName,SubmitDate,ReportDate,TradeNo,TradeDate,ProductVariety,ValueDate,FixedRatePayer,FixedRate,FloatRatePay values ( '" & strCCY & "','" & strCustomerName & "',#" & strSubmitDate & "# ,#" & strReportDate & "#,'" & strTradeNo & "',#"
& strTradeDate & "#,'" & strProductVariety & "',#" & strValueDate & "#,'" & strFixedRatePayer & "'," & strFixedRate & ",'" & strFloatRatePayer & "'," & strBPs & ")"
DoCmd.RunSQL strSql
intRow = intRow + 1
strTradeNo = ExcelWorkSheet.Cells(intRow, 1).Value
Loop

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。