VBA转换Excel数据表为SQL脚本

大佬教程收集整理的这篇文章主要介绍了VBA转换Excel数据表为SQL脚本，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

Public Sub CreateCurrentSheeTinsertScript()
Dim Row As Long
Dim Col As Integer
Application.Screenupdating = false
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
 
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get columns from the sheet
Do Until ActiveSheet.Cells(Row,Col) = "" 'Loop until you find a blank.
    ColNames(ColCount) = "[" & ActiveSheet.Cells(Row,Col) & "]"
    ColCount = ColCount + 1
    Col = Col + 1
Loop
ColCount = ColCount - 1
 
'Inputs for the starTing and ending point for the rows
Row = Val(InputBox("Give the starTing Row No.",2))

Dim MaxRow As Long
MaxRow = Val(InputBox("Give the Maximum Row No.",ActiveSheet.[A1].End(xlDown).Row))
 
Dim filePath As String,DBname As String
'File to save the generated insert statements
filePath = "C:\\import.sql"
dbname = "DB2"
File = filePath
fHandle = FreeFile()
Open File For Output As fHandle
 
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement

Do While Row <= MaxRow
    CellColCount = 0
    'ActiveSheet.Name will give the current active sheet name
    'this can be treated as table name in the database
    StringStore = "INSERT INTO [dbo].[" & ActiveSheet.Name & "$] ( "
    Do While CellColCount <= ColCount
    StringStore = StringStore & ColNames(CellColCount)
    'To avoid "," after last column
    If CellColCount <> ColCount Then
        StringStore = StringStore & ","
    End If
    CellColCount = CellColCount + 1
    Loop
    'Here it will print "insert into [Tablename] ( [Col1],[Col2],..."
    Print #fHandle,StringStore & " ) "
     
    'For prinTing the values for the above columns
    StringStore = " VALUES ( "
    CellColCount = 0
    Do While CellColCount <= ColCount
        StringStore = StringStore & IIf(Len(Trim(ActiveSheet.Cells(Row,CellColCount + 1).value)) = 0,"NULL"," '" & replace(CStr(ActiveSheet.Cells(Row,CellColCount + 1)),"'","''") & "'")
        If CellColCount <> ColCount Then
            StringStore = StringStore & ","
        End If
        CellColCount = CellColCount + 1
    Loop
    'Here it will print "values( 'value1','value2',StringStore & ")" & vbCrLf & IIf(Row Mod 5000 = 1,"GO" & vbCrLf,"") & " "
    Row = Row + 1
Loop
Close #fHandle
Application.Screenupdating = True
SHell "CMD /C OSQL -E -d " & DBname & " -i """ & filePath & """ > nul"
MsgBox ("successfully Done")
End Sub

大佬总结

以上是大佬教程为你收集整理的VBA转换Excel数据表为SQL脚本全部内容，希望文章能够帮你解决VBA转换Excel数据表为SQL脚本所遇到的程序开发问题。

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

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。