文章/答案/技术大牛

发布

社区首页 >问答首页 >运行时错误，变量不匹配导出，缺少模块引用EXCEL VBA。

问运行时错误，变量不匹配导出，缺少模块引用EXCEL VBA。
EN

Stack Overflow用户

提问于 2020-12-17 22:29:02

回答 1查看 890关注 0票数 0

下面的代码将单元格B21设置为变量，然后引用该变量从网站下载数据。

我得到的错误:带有变量的代码(坏的)：错误:运行时错误'1004'：Expression.Error，导入代码不匹配导出。你错过了模块引用吗？

Sub DownloadDataV5()
'
' DownloadDataV5 Macro

    Range("B21").Select
    Selection.Copy
' WEBLINK CODE that takes data from cell that combines TICKER & START DATE & ENDING DATE
    Dim WEBLINK As String
    WEBLINK = Sheets("Download").Cells(21, "B").Value
' comment
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents((TICKER)))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Buy/Sell"", type text}, {""Transaction Date"", type date}, {""Acceptance DateTime"", type" & _
        " datetime}, {""Issuer Name"", type text}, {""Issuer Trading Symbol"", type text}, {""Reporting Owner Name"", type text}, {""Reporting Owner Relationship"", type text}, {""Transaction Shares"", Int64.Type}, {""Price per Share"", Currency.Type}, {""Total Value"", Currency.Type}, {""Shares Owned Following Transaction"", Int64.Type}, {""Form"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""" & _
        "Changed Type"""
    ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Download").Select
    ActiveWorkbook.Queries("Table 0").Delete
End Sub

下面的代码可以工作，但是没有用，因为它不引用变量。

Sub DownloadDataV5()
'
' DownloadDataV5 Macro

    Range("B21").Select
    Selection.Copy
' comment
    ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents((""http://insidertrading.org/index.php?sort_by=acceptance_datetime&asc=&symbol=GOOG&date_from=2016-08-03&date_to=2020-12-16&submit=+GO+&page=1"")))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Buy/Sell"", type text}, {""Transaction Date"", type date}, {""Acceptance DateTime"", type" & _
        " datetime}, {""Issuer Name"", type text}, {""Issuer Trading Symbol"", type text}, {""Reporting Owner Name"", type text}, {""Reporting Owner Relationship"", type text}, {""Transaction Shares"", Int64.Type}, {""Price per Share"", Currency.Type}, {""Total Value"", Currency.Type}, {""Shares Owned Following Transaction"", Int64.Type}, {""Form"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""" & _
        "Changed Type"""
    ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MySheet"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_0"
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Download").Select
    ActiveWorkbook.Queries("Table 0").Delete
End Sub

只是需要一个超级快速的解决办法才能让这件事奏效。会感谢所有我能得到的帮助！

excel

vba

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-12-18 00:04:39

查询公式中缺少对Weblink变量的引用

ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents((" & Chr(34) & WEBLINK & Chr(34) & ")))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Buy/Sell"", type text}, {""Transaction Date"", type date}, {""Acceptance DateTime"", type" & _
    " datetime}, {""Issuer Name"", type text}, {""Issuer Trading Symbol"", type text}, {""Reporting Owner Name"", type text}, {""Reporting Owner Relationship"", type text}, {""Transaction Shares"", Int64.Type}, {""Price per Share"", Currency.Type}, {""Total Value"", Currency.Type}, {""Shares Owned Following Transaction"", Int64.Type}, {""Form"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""" & _
    "Changed Type"""

看看我是如何连接这个变量并在这里添加双引号的：

" & Chr(34) & WEBLINK & Chr(34) & "

如果能用，请告诉我。

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/65349235

复制

相似问题

问运行时错误，变量不匹配导出，缺少模块引用EXCEL VBA。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问运行时错误，变量不匹配导出，缺少模块引用EXCEL VBA。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问运行时错误，变量不匹配导出，缺少模块引用EXCEL VBA。
EN