VBA转换Excel数据表为SQL脚本，创建当前工作表的SQL脚本文件

[vb]代码库

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', ..."
    Print #fHandle, 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