基于VBA实现Excel关闭时自动发送含MS SQL脚本的邮件

Got it, let's put this all together step by step. Here's how you can implement the generate_sql_script method, hook it up to your email code, and trigger everything when you close the Excel workbook:

Step 1: Create the generate_sql_script Function #

This function will scan your worksheet, build the CREATE TABLE, SEQUENCE, and INSERT statements matching what you'd generate manually in SSMS. Paste this into a standard module:

Function generate_sql_script(ws As Worksheet, tableName As String) As String
    Dim sql As String
    Dim headerRow As Integer, lastRow As Integer, lastCol As Integer
    Dim i As Integer, j As Integer
    Dim colName As String, dataType As String
    Dim sequenceName As String
    
    ' Set defaults (adjust these as needed)
    headerRow = 1
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
    sequenceName = tableName & "_ID_Seq"
    
    ' 1. Build CREATE TABLE statement
    sql = "CREATE TABLE " & tableName & " (" & vbCrLf
    For j = 1 To lastCol
        colName = ws.Cells(headerRow, j).Value
        ' Map Excel data types to SQL Server types (adjust mappings as needed)
        Select Case ws.Cells(headerRow + 1, j).DataType
            Case xlNumber
                If ws.Cells(headerRow + 1, j).Value = Int(ws.Cells(headerRow + 1, j).Value) Then
                    dataType = "INT"
                Else
                    dataType = "DECIMAL(18,2)"
                End If
            Case xlText, xlTextValues
                dataType = "VARCHAR(255)" ' Adjust length if needed
            Case xlDate
                dataType = "DATE"
            Case Else
                dataType = "VARCHAR(255)"
        End Select
        
        ' Add column definition (mark first column as identity if needed)
        If j = 1 Then
            sql = sql & "    " & colName & " " & dataType & " PRIMARY KEY," & vbCrLf
            ' 2. Build SEQUENCE statement for identity column
            sql = sql & vbCrLf & "CREATE SEQUENCE " & sequenceName & vbCrLf
            sql = sql & "    START WITH 1" & vbCrLf
            sql = sql & "    INCREMENT BY 1;" & vbCrLf & vbCrLf
        Else
            sql = sql & "    " & colName & " " & dataType & "," & vbCrLf
        End If
    Next j
    ' Remove trailing comma and close CREATE TABLE
    sql = Left(sql, Len(sql) - 3) & vbCrLf & ");" & vbCrLf & vbCrLf
    
    ' 3. Build INSERT statements
    sql = sql & "INSERT INTO " & tableName & " ("
    ' Add column list
    For j = 1 To lastCol
        sql = sql & ws.Cells(headerRow, j).Value
        If j < lastCol Then sql = sql & ", "
    Next j
    sql = sql & ") VALUES" & vbCrLf
    
    ' Add rows of values
    For i = headerRow + 1 To lastRow
        sql = sql & "    ("
        For j = 1 To lastCol
            Select Case ws.Cells(i, j).DataType
                Case xlNumber
                    sql = sql & ws.Cells(i, j).Value
                Case xlText, xlTextValues
                    sql = sql & "'" & Replace(ws.Cells(i, j).Value, "'", "''") & "'"
                Case xlDate
                    sql = sql & "'" & Format(ws.Cells(i, j).Value, "yyyy-MM-dd") & "'"
                Case Else
                    sql = sql & "'" & Replace(ws.Cells(i, j).Value, "'", "''") & "'"
            End Select
            If j < lastCol Then sql = sql & ", "
        Next j
        sql = sql & ")"
        If i < lastRow Then sql = sql & "," & vbCrLf Else sql = sql & ";" & vbCrLf
    Next i
    
    generate_sql_script = sql
End Function

Note: Adjust the data type mappings, table name, sequence name, and header row number to match your actual worksheet structure.

Step 2: Update Your Email Sending Code #

Modify your existing email VBA to use the generated script, save it as a temporary .sql file, and attach it to the email. Here's how to integrate it:

Sub send_sql_email()
    Dim objOutlook As Object
    Dim objMail As Object
    Dim sqlScript As String
    Dim tempPath As String
    Dim ws As Worksheet
    
    ' Set the worksheet you want to process (adjust name as needed)
    Set ws = ThisWorkbook.Worksheets("YourDataSheet")
    
    ' Generate the SQL script
    sqlScript = generate_sql_script(ws, "YourTableName")
    
    ' Create temporary SQL file
    tempPath = Environ("TEMP") & "\AutoGenerated_SQL_Script.sql"
    Open tempPath For Output As #1
    Print #1, sqlScript
    Close #1
    
    ' Set up Outlook email
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    
    With objMail
        .To = "recipient@example.com" ' Adjust recipient
        .Subject = "Auto-Generated SQL Script for " & ws.Name
        .Body = "Attached is the auto-generated SQL script containing CREATE TABLE, SEQUENCE, and INSERT statements."
        .Attachments.Add tempPath
        .Send ' Use .Display instead of .Send to preview first
    End With
    
    ' Clean up temporary file
    Kill tempPath
    
    ' Release objects
    Set objMail = Nothing
    Set objOutlook = Nothing
End Sub

Step 3: Trigger on Workbook Close #

To run this automatically when you close the workbook, paste this into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Optional: Add a confirmation prompt
    If MsgBox("Do you want to generate and send the SQL script email?", vbYesNo) = vbYes Then
        send_sql_email
    End If
End Sub

Important Notes: #

• Enable macros in Excel for this to work (you'll need to save the workbook as .xlsm).
• Adjust all placeholders (worksheet name, table name, email recipient, data types) to match your specific setup.
• Test the send_sql_email sub manually first to make sure the script is generated correctly and the email sends.

内容的提问来源于stack exchange，提问作者Dave