VBA转换Excel数据表为SQL脚本

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

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

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!