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(Trim(Me.txt_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=" & Trim(Me.txt_Import.Value) & "].[" & strWorkSheetName & "$]"
    DoCmd.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,FloatRatePayer,BPs) 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