时间戳

首页 > 解决方案 > .Refresh BackgroundQuery:=False ERROR excel vba

excel - .Refresh BackgroundQuery:=False ERROR excel vba

问题描述

我在“.Refresh BackgroundQuery:=False”行收到错误

这个宏过去在我的机器上工作正常，当我转移到虚拟机时它没有工作，我得到的错误是这样的：

在此处输入图像描述

我的代码：

请注意，所有路径都已更改为新路径，所以没问题

Sub Csv()

Debug.Print ThisWorkbook.Path
Application.DisplayAlerts = False

 Workbooks.Add
    ActiveWorkbook.Queries.Add Name:="account_Transactions_Other", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Alfalakbot4\Desktop\New folder\account_Transactions_Other.txt""),[Delimiter=""#(tab)"", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, " & _
        "{""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Change Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=account_Transactions_Other;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [account_Transactions_Other]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "account_Transactions_Other"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.ListObjects("account_Transactions_Other").Unlist
    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Normal"
    Columns("A:A").Select
    Selection.NumberFormat = "@"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheets("Sheet1").Select
    ActiveWindow.SelectedSheets.Delete
    Range("A1").Select

Workbooks(2).Activate
  
End Sub

标签： excelvba

解决方案

ListObject.Refresh不采用任何参数，并且仅适用于链接到 SharePoint 的列表。

https://docs.microsoft.com/en-us/office/vba/api/excel.listobject.refresh

推荐阅读