Farseer

DAX2012学习中......

导航

统计

多个文本文件分别拷贝到同一个Excel的不同Sheet

遇到一个需求,需要将多个文件拷贝到同一个Excel的不同Sheet中,每个文本文件一个Sheet,Sheet的名字用文本文件的名字,使用VBA可以很方便地实现这个功能,不过一直对于VB的语法有些生疏,放在这里做备份。

Sub importTextFiles()
'
' Import Text Files to a Excel File.
'

'
Dim FilePath, FileName
FilePath = "D:\Items\"
FileName = Dir(FilePath + "*.txt")


Do While FileName <> ""

    Sheets.Add after:=Worksheets(Worksheets.Count)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FilePath & FileName _
        , Destination:=Range("$A$1"))
        .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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(2, 1, 2, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1:D1").Select
    Selection.Font.Bold = True
    ActiveSheet.Name = Left(FileName, Len(FileName) - 4)
    FileName = Dir()
    
Loop
End Sub


 

posted on 2013-01-04 22:22  佛西亚  阅读(...)  评论(... 编辑 收藏