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

[vb]代码库

Public Sub CreateAllSheetsInsertScript()
Dim Row As Long
Dim Col As Integer
  
'To store all the columns available in the all of the worksheets
Dim ColNames(100) As String
Dim ColCount As Integer
 Dim MaxRow As Long
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement
Dim filePath As String, DBname As String
DBname = "DB2"
Application.ScreenUpdating = False
'File to save the generated insert statements
filePath  =  "C:\\import.sql"
File = filePath
fHandle = FreeFile()
Open File For Output As fHandle
Dim sh As Worksheet
For 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
            Do While Row <= MaxRow
            CellColCount = 0
            '.Name will give the current active sheet name
            'this can be treated as table name in the database
            StringStore = "INSERT INTO [dbo].[Material_price_new] ( "
            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(.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', ..."
            Print #fHandle, StringStore & ")" & vbCrLf & IIf(Row Mod 5000 = 1, "GO" & vbCrLf, "") & " "
            Row = Row + 1
        Loop
    End With
Next sh
Close #fHandle
Application.ScreenUpdating = True
Shell "CMD /C OSQL -E -d " & DBname & " -i """ & filePath & """ > nul"
MsgBox ("Successfully Done")
End Sub