如何在VBA中插入到临时表

Question

如何在VBA中插入到临时表

3

当我运行以下代码时，会收到一个错误提示："运行时错误'1004': 应用程序或对象定义的错误"

当我选择调试时，会突出显示以下行：.Refresh BackgroundQuery:=False

querystr = "SET NOCOUNT ON" & Chr(13) & _
        "SELECT CSD.StoreNo AS 'StoreNo',SUM(CSD.Amount) as totalSales " & Chr(13) & _
        "INTO    #SalesOfTheStores " & Chr(13) & _
        "FROM Purchase.dbo.CashsheetDetail as CSD " & Chr(13) & _
        "INNER JOIN Purchase.dbo.CashsheetHeader as CSH on CSH.TransferID = CSD.TransferID and CSH.StoreNo = CSD.StoreNo " & Chr(13) & _
        "WHERE  CSD.Comments = 'Total Gross Sales' AND CSH.DayendDate between '" & StartDate & "' And '" & EndDate & "' " & Chr(13) & _
        "GROUP BY CSD.StoreNo; "



With ActiveSheet.QueryTables.Add(Connection:= _
    "ODBC;DRIVER=SQL Server;SERVER=" & Div & "DBL01\SR;UID=" & User & ";APP=Microsoft Office 2003;WSID=" & PC & ";DATABASE=Purchase;Trusted_Connection=Yes" _
    , Destination:=Range("A1"))
    .RefreshStyle = xlOverwriteCells
    .CommandText = querystr
    .Refresh BackgroundQuery:=False

End With

- Adam

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rachel Hettinger · Answer 1

0

您的查询字符串未返回数据。请将SQL更改为SELECT语句，而不是SELECT INTO（删除INTO子句）。

- Rachel Hettinger

谢谢回复，但我想将查询结果存储到一个临时表中运行。 - Adam

1

一个 QueryTable 需要数据；如果你向临时表 INSERT INTO 数据，那么该表将会被创建在你的 SQL 数据库中，而不是返回到 Excel。 - Rachel Hettinger