- 帖子
- 6
- 主題
- 1
- 精華
- 0
- 積分
- 6
- 點名
- 0
- 作業系統
- window xp
- 軟體版本
- Office 2003
- 閱讀權限
- 10
- 性別
- 男
- 註冊時間
- 2013-10-8
- 最後登錄
- 2013-11-28
|
4#
發表於 2013-10-11 11:02
| 只看該作者
回復 3# wj1228.518
Sub 匯入文字檔()
Dim MySht As Worksheet, uFile, ShtName, xSht As Worksheet, i&
Set MySht = Sheets("TEMP")
Application.ScreenUpdating = False
ShtName = Array("0", "430", "630", "1040", "1050", "2050", "3050")
'---------------------------------------
For i = 0 To UBound(ShtName)
uFile = ThisWorkbook.Path & "\" & ShtName(i) & ".txt"
If Dir(uFile) = "" Then GoTo NEXT_FILE
'----------------------------------------
For Each xSht In Sheets
If xSht.Name = ShtName(i) Then
Application.DisplayAlerts = False
xSht.Delete
Application.DisplayAlerts = True
End If
Next
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = ShtName(i)
'-----------------------------------------
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & uFile, Destination:=Range("A1"))
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 950
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 9, 11, 8, 5, 11, 11, 8, 8, 10)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete
End With
NEXT_FILE:
Next
'-------------------------------------------------------
MySht.Select
Application.ScreenUpdating = True
MsgBox "∼∼匯入完成∼∼ "
End Sub |
|
