Option Explicit

Sub copydata()

   Dim Filename As String
   Dim MyPath As String
   Dim fullpath As String
   MyPath = BrowseForFolder & "\"
   Filename = Dir(MyPath)
   fullpath = MyPath & Filename
   Do While Filename <> ""
       If Filename <> "." And Filename <> ".." Then
           If Filename Like "*.txt" Then
               With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fullpath, _
                    Destination:=Range("A1"))
                   .Name = Filename
                   .FieldNames = True
                   .RowNumbers = False
                   .FillAdjacentFormulas = False
                   .PreserveFormatting = True
                   .RefreshOnFileOpen = False
                   .RefreshStyle = xlInsertDeleteCells
                   .SavePassword = False
                   .SaveData = True
                   .AdjustColumnWidth = True
                   .RefreshPeriod = 0
                   .TextFilePromptOnRefresh = False
                   .TextFilePlatform = 866
                   .TextFileStartRow = 1
                   .TextFileParseType = xlFixedWidth
                   .TextFileTextQualifier = xlTextQualifierDoubleQuote
                   .TextFileConsecutiveDelimiter = False
                   .TextFileTabDelimiter = True
                   .TextFileSemicolonDelimiter = False
                   .TextFileCommaDelimiter = False
                   .TextFileSpaceDelimiter = False
                   .TextFileColumnDataTypes = Array(1, 1, 1)
                   .TextFileFixedColumnWidths = Array(1, 12)
                   .TextFileTrailingMinusNumbers = True
                   .Refresh BackgroundQuery:=False 'Вот здесь он выдаёт ошибку 1004.
               End With
                   SetAttr fullpath, vbNormal
                   Kill fullpath
           End If
       End If
       Filename = Dir
   Loop
End Sub

Function BrowseForFolder(Optional OpenAt As Variant)
'Function purpose:  To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE:  If invalid, it will open at the Desktop level

   Dim ShellApp As Object
   
   'Create a file browser window at the default folder
   Set ShellApp = CreateObject("Shell.Application"). _
       BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
   
   'Set the folder to that selected.  (On error in case cancelled)
   On Error Resume Next
       BrowseForFolder = ShellApp.self.Path
   On Error GoTo 0
   
   'Destroy the Shell Application
   Set ShellApp = Nothing
   
   'Check for invalid or non-entries and send to the Invalid error
   'handler if found
   'Valid selections can begin L: (where L is a letter) or
   '\\ (as in \\servername\sharename.  All others are invalid
   Select Case Mid(BrowseForFolder, 2, 1)
       Case Is = ":"
           If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
       Case Is = "\"
           If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
       Case Else
           GoTo Invalid
   End Select
   
   Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
   BrowseForFolder = False

End Function