[大数据]VBA代码片之跨SQL处理工作表数据-插入

Option Explicit
Sub runSql()
    Dim Sql1 As String, Sql2 As String, Sql3 As String, t As String, Sql As String
    Dim conn As Object, i As Integer
    Dim fileName As String
    
    Set conn = CreateObject("ADODB.Connection")     '创建一个连接对象
    fileName = "C:\Users\qwq\Desktop\SQL练习.xlsx"
    conn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & fileName '打开文件连接

    t = "b.枝江市参考信息价 AS 枝江市参考信息价,b.夷陵区参考信息价 AS 夷陵区参考信息价," _
      & "b.兴山县参考信息价 AS 兴山县参考信息价,b.远安县参考信息价 AS 远安县参考信息价," _
      & "b.当阳市参考信息价 AS 当阳市参考信息价,b.长阳县参考信息价 AS 长阳县参考信息价," _
      & "b.秭归县参考信息价 AS 秭归县参考信息价,b.宜都市参考信息价 AS 宜都市参考信息价," _
      & "b.五峰县参考信息价 AS 五峰县参考信息价"
        
    Sql1 = "SELECT a.*," & t & " FROM [对比表$] AS a " _
        & "LEFT JOIN [1月基本信息价格$] as b " _
        & "ON a.材质=b.材质 AND a.规格（mm）=b.规格（mm）" _
        & " WHERE a.时间>=#2022/1/1# AND a.时间<=#2022/1/31#"
        
   Sql2 = "SELECT a.*," & t & " FROM [对比表$] AS a " _
        & "LEFT JOIN [2月基本信息价格$] as b " _
        & "ON a.材质=b.材质 AND a.规格（mm）=b.规格（mm）" _
        & " WHERE a.时间>=#2022/2/1# AND a.时间<=#2022/2/28#"
     
    Sql3 = "SELECT a.* FROM [对比表$] AS a " _
        & " WHERE a.时间>=#2022/3/1# AND a.时间<=#2022/3/31#"
    Sql = "INSERT INTO [Sheet1$] " & Sql3
    For i = 1 To 3
        conn.Execute Sql
        Debug.Print i
    Next i
    
    
    conn.Close
    Set conn = Nothing
    MsgBox "OK"
End Sub

《新程序员》：云原生和全面数字化实践

50位技术专家共同创作，文字、视频、音频交互阅读