Excel 2010中的运行时错误1004刷新BackgroundQuery

问题描述

我想在vba中编写一个脚本，用于将多个文本文件导入到excel(一张纸)中，然后将它们绘制在一张图上.我在刷新BackgroundQuery 命令中遇到问题，并遇到1004运行时错误.

I am tring to write a script in vba for importing several text files to excel (one sheet) and than draw them on one graph.I am facing a problem in Refresh BackgroundQuery commant and falls on 1004 run time error.

我该如何解决?

谢谢，没事

这是我的代码:

Sub fring1()

    Dim fpath As String
    Dim fname As String
    Dim i As Integer

    fpath = "C:\Users\epinkas\Desktop\Yossi\"
    fname = fpath & "*.txt"

    Name = Dir(fname)
    While Name <> ""

        With Sheet1.QueryTables.Add(Connection:= _
          "TEXT;fpath & Name", _
          Destination:=Range("$A$1"))
            .Name = fpath & Name
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
        ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$A$1356")

        Name = Dir()
    Wend

End Sub

推荐答案

您似乎正在尝试在带引号的字符串中使用路径和文件名变量.将变量连接到带引号的字符串中.

It looks like you are trying to use your path and filename variables inside a quoted string. Concatenate the variables into the quoted string.

    With Sheet1.QueryTables.Add(Connection:= _
      "TEXT;" & fpath & Name, _
      Destination:=Range("$A$1"))

那应该将变量的值而不是变量名放在字符串中.

That should put the values of the variables into the string, not their variables names.

这篇关于Excel 2010中的运行时错误1004刷新BackgroundQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！