本人小白一個， 所以一開始先錄制了一個宏， 本想在這基礎上改一下， 按格式匯入"E:\Sir\老王"底下的所有txt， 但是無論用InStr 還是 like 都提示出錯。求各位指點一下
以下是兩個原本錄制出來的宏，沒有加通配符：
Sub 宏6()
'
' 宏6 宏
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Sir\老王\SC20111024.txt", Destination:=Range("$A1"))
.Name = "SC20111024"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = 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
.Refresh BackgroundQuery:=False
End With
End Sub
Sub 宏3()
'
' 宏3 宏
'
'
ChDir "E:\Sir\老王"
Workbooks.OpenText Filename:="SC20111024.txt", Origin:=936 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, 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), Array(16, 1), 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
uj5u.com熱心網友回復：
Sub 批量匯入()Dim stn As String
Dim wk As Workbook
stn = ActiveSheet.Name
myPath = "E:\老王\"
myfile = Dir(myPath & "*.txt")
Do While myfile <> ""
ss = myPath & myfile
Workbooks.OpenText Filename:=ss, Origin:=936, 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( _
18, 1), Array(19, 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).Range("a65536").End(3).Offset(1, 0).Resize(UBound(arr), 23).NumberFormatLocal = "@"
ThisWorkbook.Sheets(stn).Range("a65536").End(3).Offset(1, 0).Resize(UBound(arr), 23) = arr
Workbooks(myfile).Close
myfile = Dir
Loop
End Sub
uj5u.com熱心網友回復：
把第五行的"E:\老王\"把為"E:\Sir\老王"轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/130486.html
標籤：VBA