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