[發問] 文字檔以覆蓋或貼上方式匯入EXCEL指定欄位

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2014-3-26 16:43 | 只看該作者

請問要將文字檔內容匯入至指定欄位中，因指定欄位皆有對應計算公式，故需要以貼上或覆蓋的方式匯入而不是以插入方式匯入資料（這樣後續計算公式會亂掉），請問下面內容要如何修改才能達成呢？？

Sub OpenFile()
Dim strFilt As String
Dim strTitle As String
Dim strFname As Variant
Dim i As Integer
Dim strMsg As String
strFilt = "文字檔案,*.txt,"
strTitle = "打開Excel文件"
strFname = Application.GetOpenFilename(FileFilter:=strFilt, Title:=strTitle, MultiSelect:=True)
If Not IsArray(strFname) Then
MsgBox "沒選擇文件！"
Else
For i = LBound(strFname) To UBound(strFname)
strMsg = strMsg & strFname(i) & vbCrLf
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFname(i), Destination:=Range("$S$2"))
.Name = "18"
.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)
.TextFileFixedColumnWidths = Array(14)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
Columns("S:AG").Select
Range("S2").Activate
Selection.ColumnWidth = 3
End With
Next
MsgBox "選擇的文件是：" & vbCrLf & strMsg
End If
End Sub