VBA：。更新运行时错误 [英] VBA: .Refresh Run-Time Error

问题描述

我有一些VBA代码有问题。我在Windows 7 Enterprise上运行Excel 2010。

我正在尝试从一个文件夹中读取几个制表符分隔的文本文件，并将它们放在一个单独的工作表中Excel工作簿。为此，我使用的是查询表。在调试中，我有一个的问题.Refresh BackgroundQuery：= False 。当它达到这一行时，它会引发1004运行时错误，指出Excel无法找到文本文件来刷新此外部数据范围。我不知道为什么会发生这种情况。我知道查询表不会被创建，直到它读取这一行，这使调试困难。这是代码。任何帮助将不胜感激。感谢提前！

  Sub LoadPipeDelimitedFiles（）
 Dim idx As Integer 
 Dim fname As String 
 
 idx = 0 
 fname = Dir（C：\files\ * .txt）
 While（Len（fname）> 0）
 idx = idx + 1 
 Sheets（Sheet& idx）。选择
使用ActiveSheet.QueryTables.Add（Connection：=TEXT;& fname，Destination：= Range（A1）） 
 .Name =a& idx 
 .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 
 .TextFileOtherDelimiter = False 
 .TextFileColumnDataTypes = Array（1，1，1）
 .TextFileTrailingMinusNumbers = True 
 .Refresh BackgroundQuery：= False 
 fname = Dir 
结束
 Wend 
 End Sub 
  

这是更正：

  Sub LoadPipeDelimitedFiles（）
 Dim idx As Integer 
 Dim fpath As String 
 Dim fname As String 
 Dim f_dummy As String 
 
 idx = 0 
 fpath =C：\files\ 
 f_dummy = fpath& * .txt
 fname = Dir（f_dummy）
 While（Len（fname）> 0）
 idx = idx + 1 
表格（Sheet& idx ）。选择
使用ActiveSheet.QueryTables.Add（Connection：=TEXT;_ 
& fpath& fname，Destination：= Range（A1））
 .Name = a& idx 
 .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 
 .TextFileOtherDelimiter = False 
 .TextFileColumnDataTypes = Array（1，1，1）
 .TextFileTrailingMinusNumbers = True 
 .Refresh BackgroundQuery：= False 
 fname = Dir 
结束
 Wend 
 End Sub 
  

解决方案

使用ActiveSheet.QueryTables.Add更改行（Connection：=TEXT;& fname，Destination：= Range（A1））

to

With ActiveSheet.QueryTables.Add（Connection：=TEXT;&C：\files\& fname，Destination：= Range（A1））

您 fname 只有文件的名称，而不是完整路径

还要避免使用。选择并完全限定您的对象。
兴趣阅读

您的代码可以写成

  Sub LoadPipeDelimitedFiles（）
 Dim idx As Integer 
 Dim fname As String，FullName As String 
 Dim ws As Worksheet 
 
 idx = 0 
 
 fname = Dir（C：\ * .txt）
 
 While（Len（fname）> 0）
 FullName =C：\& fname 
 idx = idx + 1 
 
设置ws = ThisWorkbook.Sheets（Sheet& idx）
 
使用ws.QueryTables.Add（Connection = TEXT;& _ 
 FullName，Destination：= ws.Range（A1））
'
'~~>其余的代码
'
 fname = Dir 
结束
 Wend 
 End Sub 
  

I am having a problem with some VBA code. I'm running Excel 2010 on Windows 7 Enterprise.

I'm trying to read in several tab-delimited text files from a folder and put them onto separate sheets in one Excel workbook. To do this, I'm using a Query Table. In debugging, I have a problem with .Refresh BackgroundQuery:=False. When it reaches this line, it throws a 1004 run-time error, stating that Excel cannot find the text file to refresh this external data range. I don't know why this is occurring. I know that the Query Table isn't created until it reads this line, which makes debugging difficult. Here is the code. Any help would be much appreciated. Thanks in advance!

Sub LoadPipeDelimitedFiles()
Dim idx As Integer
Dim fname As String

idx = 0
fname = Dir("C:\files\*.txt")
While (Len(fname) > 0)
    idx = idx + 1
    Sheets("Sheet" & idx).Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))
        .Name = "a" & idx
        .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
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend
End Sub

Here is the correction:

Sub LoadPipeDelimitedFiles()
Dim idx As Integer
Dim fpath As String
Dim fname As String
Dim f_dummy As String

idx = 0
fpath = "C:\files\"
f_dummy = fpath & "*.txt"
fname = Dir(f_dummy)
While (Len(fname) > 0)
    idx = idx + 1
    Sheets("Sheet" & idx).Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
      & fpath & fname, Destination:=Range("A1"))
        .Name = "a" & idx
        .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
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        fname = Dir
    End With
Wend
End Sub

解决方案

Change the line With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fname, Destination:=Range("A1"))

to

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\files\" & fname, Destination:=Range("A1"))

You fname just has the name of the file and not the full path

Also avoid using .Select and fully qualify your Objects. INTERESTING READ

Your code can be written as

Sub LoadPipeDelimitedFiles()
    Dim idx As Integer
    Dim fname As String, FullName As String
    Dim ws As Worksheet

    idx = 0

    fname = Dir("C:\*.txt")

    While (Len(fname) > 0)
        FullName = "C:\" & fname
        idx = idx + 1

        Set ws = ThisWorkbook.Sheets("Sheet" & idx)

        With ws.QueryTables.Add(Connection:="TEXT;" & _
        FullName, Destination:=ws.Range("A1"))
            '
            '~~> Rest of the code
            '
            fname = Dir
        End With
    Wend
End Sub

这篇关于VBA：。更新运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！