Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open many *.tsv files in folder and import the data into Excel
I would like to import each tsv file as a separate sheet in same excel
workbook. Is there any way to do it? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open many *.tsv files in folder and import the data into Excel
Use this macro then if the files are in C:\Data
More info here http://www.rondebruin.nl/copy3.htm Sub Example12() Dim MyPath As String Dim FilesInPath As String Dim MyFiles() As String Dim SourceRcount As Long Dim Fnum As Long Dim mybook As Workbook Dim basebook As Workbook 'Fill in the path\folder where the files are 'on your machine MyPath = "c:\Data" 'Add a slash at the end if the user forget it If Right(MyPath, 1) < "\" Then MyPath = MyPath & "\" End If 'If there are no Excel files in the folder exit the sub FilesInPath = Dir(MyPath & "*.csv") If FilesInPath = "" Then MsgBox "No files found" Exit Sub End If On Error GoTo CleanUp Application.ScreenUpdating = False Set basebook = ThisWorkbook 'Fill the array(myFiles)with the list of Excel files in the folder Fnum = 0 Do While FilesInPath < "" Fnum = Fnum + 1 ReDim Preserve MyFiles(1 To Fnum) MyFiles(Fnum) = FilesInPath FilesInPath = Dir() Loop 'Loop through all files in the array(myFiles) If Fnum 0 Then For Fnum = LBound(MyFiles) To UBound(MyFiles) Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum)) mybook.Worksheets(1).Copy after:= _ basebook.Sheets(basebook.Sheets.Count) On Error Resume Next ActiveSheet.Name = mybook.Name On Error GoTo 0 ' You can use this if you want to copy only the values ' With ActiveSheet.UsedRange ' .Value = .Value ' End With mybook.Close savechanges:=False Next Fnum End If CleanUp: Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "SunRace" wrote in message ... I would like to import each tsv file as a separate sheet in same excel workbook. Is there any way to do it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Open Certain Excel Files in Folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
Open Excel files in a folder | Excel Programming | |||
Open Excel files in a folder | Excel Programming |