My small white one, so the first recorded a macro, wanted to on the basis of this change, according to the format to import "E: \ Sir \ Lao wang" under all the TXT, but no matter use InStr or the like, tip error, ask everybody to give directions

The following are the two original recording macros, without wildcards:
Sub macro (6)
'
"Acer 6
'

'
With ActiveSheet. QueryTables. Add (Connection:=_
"TEXT. E: \ Sir \ Lao wang \ SC20111024 TXT ", Destination:=Range (" $A1 "))
.name="SC20111024"
The FieldNames=True
. RowNumbers=False
. FillAdjacentFormulas=False
. PreserveFormatting=True
. RefreshOnFileOpen=False
. RefreshStyle=xlInsertDeleteCells
. SavePassword=False
. A SaveData=https://bbs.csdn.net/topics/True
. AdjustColumnWidth=True
. RefreshPeriod=0
. TextFilePromptOnRefresh=False
TextFilePlatform=936
. TextFileStartRow=1
. TextFileParseType=xlDelimited
. TextFileTextQualifier=xlTextQualifierDoubleQuote
. TextFileConsecutiveDelimiter=False
. TextFileTabDelimiter=True
. TextFileSemicolonDelimiter=False
. TextFileCommaDelimiter=False
. TextFileSpaceDelimiter=False
. TextFileOtherDelimiter="|" "
TextFileColumnDataTypes=Array (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
. TextFileTrailingMinusNumbers=True
The Refresh BackgroundQuery:=False
End With
End Sub


Sub macro (3)
'
'acer 3
'

'
ChDir "E: \ Sir \ Lao wang"
Workbooks. OpenText Filename:="SC20111024. TXT", Origin:=936 _
, while the StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon, Comma:==False, or False on _
And Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array (Array (1, 1 _
), Array (2, 1), Array (3, 1), Array (4, 1), Array (5, 1), Array (6, 1), Array (7, 1), Array (8, 1), _
Array (9, 1), Array (10, 1), Array (11, 1), Array (12, 1), Array (13, 1), Array (14, 1), Array (15 _
1, 1), Array (16), Array (17, 1), Array (18, 1), Array (19, 1), Array (20, 1), Array (21, 1), _
Array (22, 1), Array (23, 1)), TrailingMinusNumbers:=True
End Sub

CodePudding user response:

Sub bulk import ()
Dim the STN As String
Dim wk As a Workbook
The STN=ActiveSheet. Name

MyPath="E: \ Lao wang "
Myfile=Dir (myPath & amp; "*.txt)

The Do While myfile & lt;> "
"
Ss=myPath & amp; Myfile

Workbooks. OpenText Filename:=ss, Origin:=936, while the StartRow:=1, _
DataType:=xlDelimited TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:="|", FieldInfo:=Array (Array (1, 2), Array (2, 1), Array (3, 1), Array (4, 1), _
Array (5, 1), Array (6, 1), Array (7, 1), Array (8, 1), Array (9, 1), Array (10, 1), Array (11, 1), _
Array (12, 1), Array (13, 1), Array (14, 1), Array (15, 1), Array (16, 1), Array (17, 1), Array (_
19, 18, 1), Array (1), Array (20, 1), Array (21, 1), Array (22, 1), Array (23, 1)), TrailingMinusNumbers:=True

Ms=Split (myfile, ". ")

Arr=Workbooks (myfile.) Sheets (ms (0)) UsedRange. Value

ThisWorkbook. Sheets (STN). The Range (" a65536 "). The End (3). The Offset (1, 0). The Resize (UBound (arr), 23). NumberFormatLocal="@"
ThisWorkbook. Sheets (STN). The Range (" a65536 "). The End (3). The Offset (1, 0). The Resize (UBound (arr), 23)=arr

Workbooks (myfile.) Close

Myfile=Dir

Loop

End Sub

CodePudding user response:

The fifth line "E: \ Lao wang " take to "E: \ Sir \ Lao wang"