[算表] VBA自動匯入TXT檔的問題

看板Office作者BioWu (過客)時間14年前 (2012/04/21 15:56)推噓0(0推 0噓 8→)

留言8則, 3人參與討論串1/1

軟體: MS EXCEL 版本:2003 目前有一個需求是匯入少量TXT檔，每TXT檔為一新分頁，並重新命名分頁名稱為檔案名稱的某部份。我用錄製巨集並修改了VBA程式如下 Sub 匯入() Dim Num As Integer, i As Integer Dim Mypath As String With Application.FileSearch .LookIn = ThisWorkbook.Path .Filename = "*.TXT" .Execute Num = .FoundFiles.Count For i = 1 To Num ActiveWorkbook.Worksheets.Add With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & .FoundFiles(i), Destination:=Range( _ "A1")) .Name = .FoundFiles(i) 目前在這個部份遇到問題，我不知道怎麼把TXT的檔名提取出來，好讓他寫在.Name = 的後面 .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 950 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = True .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False ActiveSheet.Name = Left(.Name, 8) End With Next i End With End Sub 請各位先進幫忙，另外可否推薦VBA入門的書，我有搜尋了一本叫做超圖解 Excel VBA(旗標)，想請問一下是否還有其他推薦？謝謝 -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 220.130.40.212 ※ 編輯: BioWu 來自: 220.130.40.212 (04/21 17:55)