VBA将Excel数据表格直接导入SQL Server数据库

概述VBA将Excel数据表格直接导入SQL Server数据库

下面是内存溢出 jb51.cc 通过网络收集整理的代码片段。

内存溢出小编现在分享给大家，也给大家做个参考。

Option ExplicitPublic Sub CreateallSheetsInsertScript()On Error GoTo ErrorHandler 'recordset and connection variablesDim Row As LongDim Col As Integer'To store all the columns available in the all of the worksheetsDim Colnames(100) As StringDim ColCount As IntegerDim MaxRow As LongDim CellColCount As IntegerDim StringStore As String 'Temporary variable to store partial statementDim InsertScripthead As StringDim DBname As StringDim tablename As StringDim Ret As LongDim Cnxn As New ADODB.ConnectionDBname = "DB1"tablename = "table1"Cnxn.Open "ProvIDer=sqlolEDB;Data Source=localhost;Initial Catalog=" & DBname & ";Integrated Security=sspI;"Application.ScreenUpdating = FalseDim sh As WorksheetFor Each sh In ActiveWorkbook.Sheets    With sh        .Select        Col = 1        Row = 1        ColCount = 0         'Get Columns from the sheet        Do Until .Cells(Row,Col) = "" 'Loop until you find a blank.            Colnames(ColCount) = "[" & .Cells(Row,Col) & "]"            ColCount = ColCount + 1            Col = Col + 1        Loop        ColCount = ColCount - 1        'inputs for the starting and ending point for the rows        Row = 2        MaxRow = .[A1].End(xlDown).Row        CellColCount = 0        '.name will give the current active sheet name        'this can be treated as table name in the database        InsertScripthead = "INSERT INTO [dbo].[" & tablename & "] ( "        do while CellColCount <= ColCount            InsertScripthead = InsertScripthead & Colnames(CellColCount)             'To avoID "," after last column            If CellColCount <> ColCount Then                InsertScripthead = InsertScripthead & ","            End If            CellColCount = CellColCount + 1        Loop        InsertScripthead = InsertScripthead & " ) VALUES ( "        do while Row <= MaxRow            'Here it will print "insert into [tablename] ( [Col1],[Col2],..."            'For printing the values for the above columns            StringStore = InsertScripthead            CellColCount = 0            do while CellColCount <= ColCount                StringStore = StringStore & IIf(Len(Trim(.Cells(Row,CellColCount + 1).Value)) = 0,"NulL"," '" & Replace(CStr(.Cells(Row,CellColCount + 1)),"'","''") & "'")                If CellColCount <> ColCount Then                    StringStore = StringStore & ","                End If                CellColCount = CellColCount + 1            Loop            'Here it will print "values( 'value1','value2',..."            Cnxn.Execute StringStore & ")"            Row = Row + 1        Loop    End WithNext shApplication.ScreenUpdating = True' clean upCnxn.CloseSet Cnxn = nothingMsgBox ("Successfully Done")Exit Sub    ErrorHandler:   ' clean up    If Not Cnxn Is nothing Then        If Cnxn.State = adStateOpen Then Cnxn.Close    End If    Set Cnxn = nothing        If Err <> 0 Then        MsgBox Err.source & "-->" & Err.Description,"Error"    End IfEnd Sub

以上是内存溢出(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

总结

以上是内存溢出为你收集整理的VBA将Excel数据表格直接导入SQL Server数据库全部内容，希望文章能够帮你解决VBA将Excel数据表格直接导入SQL Server数据库所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。