从文件夹目录导入所有类型的文件（cvs，xls，txt）到一个主excel文件

问题描述：

我正在处理一个宏，需要选择我想要的任何文件夹并将该文件夹中的每个类型的文件导入为cvs ，xls，txt并将它们全部放入1个工作簿（不是工作表）中。所以导入的所有标签都会在那里。目前代码只能采用1种类型。我试着将以下代码更改为：从文件夹目录导入所有类型的文件（cvs，xls，txt）到一个主excel文件

fileName = Dir(directory & "*.csv, *.xls,*.txt")

但是什么也没有发生。

下面的宏有一个固定的目录路径，但我希望有一个对话框弹出，这使我可以灵活地选择任何文件夹，我想从中导入我的文件。这是我到目前为止，但请修改它或做一个最好的新的。

Sub Input_Sheets() 

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer 
Dim WrdArray() As String 

Application.ScreenUpdating = False 
Application.DisplayAlerts = False 

directory = "C:\Users\ktam\Desktop\New folder\" 

'Switch to the preferred type the folders hold. (It cannot hold 2 types) 
'fileName = Dir(directory & "*.xl??") 
fileName = Dir(directory & "*.csv") 

'As long as the file name is found in the folder, import the file. 
Do While fileName <> "" 
Workbooks.Open (directory & fileName) 'Opens a random file from the folder 
    'WrdArray() = Split(fileName, ".") 

    For Each sheet In Workbooks(fileName).Worksheets 
    'Workbooks(fileName).ActiveSheet.Name = WrdArray(0) '0 Puts in the name of the document 

       total = ThisWorkbook.Worksheets.Count 
       Workbooks(fileName).Worksheets(sheet.Name).Copy After:=ThisWorkbook.Worksheets(total) 
    Next sheet 

Workbooks(fileName).Close 
fileName = Dir() 
Loop 

Application.ScreenUpdating = True 
Application.DisplayAlerts = True 

MsgBox "Complete" 

End Sub

使用'*。*'并通过检查扩展名来筛选出想要的类型... –

优秀的Tim！这需要将我想要的所有内容导入到文档中。现在只有我可以从我选择的任何文件夹中选择它。 –

http://www.cpearson.com/excel/browsefolder.aspx查看页面上的最后一个功能 –

答

我有这样的代码，这是一个文件夹内循环和基于文件名的文件加载到一个数组非常有用：

Global sfolder As String 

sub file_merger() 

file = Dir(folderchooser) 

dim trackerfiles(1 to 500) as variant 

counter = 1 

Do While file <> "" 

    if instr(1,file,".xlsx") > 0 or instr(1,file,".csv") > 0 then 


    trackerfiles(counter) = sfolder & "\" & file 
    file = Dir() 
    counter = counter + 1 

    If file = "" Then 
     Exit Do 
    End If 

    End if 

Loop 


end sub 

Function folderchooser() As String 

With Application.FileDialog(msoFileDialogFolderPicker) 
    .Title = "Select a folder" 
    .AllowMultiSelect = False 
    .Show 
    sfolder = .SelectedItems(1) 
End With 

folderchooser = sfolder & "\" 

End Function

你可以用它加载到一个数组，然后写您自己的代码将文件导入工作簿（棘手的部分是循环浏览文件夹）。

从文件夹目录导入所有类型的文件（cvs，xls，txt）到一个主excel文件

相关推荐