如何用VBA导入几个TXT文件到同一个工作表里去。模板都一样就数据有点出入。挺急的麻烦哪位大大出手吧多谢
怎么用VBA导入几个TXT文件到同一个工作表里去。模板都一样就数据有点出入。挺急的麻烦哪位大大出手吧谢谢。
Sub 读取数据()
'made by fxw
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim newwb As Workbook
Set newwb = Workbooks.Add
newwb.Application.ActiveWindow.Caption = "临时数据.xls"
With fd
.Filters.Clear
.Filters.Add "文本文件", "*.txt", 1
.Filters.Add "所有文件", "*.*", 2
.Title = " 请选择要合并的txt文件 "
If .Show = -1 Then
Application.ScreenUpdating = False
Dim vrtSelectedItem As Variant
Dim i As Integer
i = 1
For Each vrtSelectedItem In .SelectedItems
Dim tempwb As Workbook
Set tempwb = Workbooks.Open(vrtSelectedItem)
tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)
newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".txt", "")
tempwb.Close savechanges:=False
i = i + 1
Next vrtSelectedItem
Else:
newwb.Close savechanges:=False
Exit Sub
End If
End With
Sheets(1).Select
Range("A1").Select
If ActiveWorkbook.Sheets.Count > 3 Then
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet3").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
Application.ScreenUpdating = True
Selection.AutoFilter '筛选
End Sub
大家帮帮忙吧。困在这里面一个星期了。现在是每一个TXT文件生成一个工作表,我想要的是不管选择几个TXT文件都输出在同一个表上。
------解决方案--------------------
楼主试试这个:
Sub 读取数据()
'made by fxw
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim newwb As Workbook
Set newwb = Workbooks.Add
newwb.Application.ActiveWindow.Caption = "临时数据.xls"
With fd
.Filters.Clear
.Filters.Add "文本文件", "*.txt", 1
.Filters.Add "所有文件", "*.*", 2
.Title = " 请选择要合并的txt文件 "
If .Show = -1 Then
Application.ScreenUpdating = False
Dim vrtSelectedItem As Variant
Dim i As Integer
i = 1
For Each vrtSelectedItem In .SelectedItems
Dim tempwb As Workbook
Set tempwb = Workbooks.Open(vrtSelectedItem)
tempwb.Worksheets(1).Copy Before:=newwb.Worksheets(i)
newwb.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".txt", "")
tempwb.Close savechanges:=False
i = i + 1
Next vrtSelectedItem
Else:
newwb.Close savechanges:=False
Exit Sub
End If
End With
Sheets(1).Select
Range("A1").Select
If ActiveWorkbook.Sheets.Count > 3 Then
Sheets("Sheet1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Sheet3").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End If
Application.ScreenUpdating = True
Selection.AutoFilter '筛选
End Sub
大家帮帮忙吧。困在这里面一个星期了。现在是每一个TXT文件生成一个工作表,我想要的是不管选择几个TXT文件都输出在同一个表上。
------解决方案--------------------
楼主试试这个:
Sub 读取数据()
'made by fxw
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim NewWB As Workbook
Set NewWB = Workbooks.Add
NewWB.Application.ActiveWindow.Caption = "临时数据.xls"
With fd
.Filters.Clear
.Filters.Add "文本文件", "*.txt", 1
.Filters.Add "所有文件", "*.*", 2
.Title = " 请选择要合并的txt文件 "
If .Show = -1 Then