Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple csv files into individual tabs
In Excel from the Open File window I want to select multiple csv files and
have them load into individual worksheets (tabs), not individual workbooks. Does anyone know how to do this? It would be great if there were a check box option on the Open File window to select individual files (workbooks) or individual tabs (worksheets in a file). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple csv files into individual tabs
Hi
Try this Option Explicit 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 csv 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/tips.htm "dna1711" wrote in message ... In Excel from the Open File window I want to select multiple csv files and have them load into individual worksheets (tabs), not individual workbooks. Does anyone know how to do this? It would be great if there were a check box option on the Open File window to select individual files (workbooks) or individual tabs (worksheets in a file). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple csv files into individual tabs
Oops
Try this for all files in MyPath = "C:\Data" B -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Try this Option Explicit 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 csv 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/tips.htm "dna1711" wrote in message ... In Excel from the Open File window I want to select multiple csv files and have them load into individual worksheets (tabs), not individual workbooks. Does anyone know how to do this? It would be great if there were a check box option on the Open File window to select individual files (workbooks) or individual tabs (worksheets in a file). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple csv files into individual tabs
Thank you much - it works great!
"Ron de Bruin" wrote: Hi Try this Option Explicit 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 csv 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/tips.htm "dna1711" wrote in message ... In Excel from the Open File window I want to select multiple csv files and have them load into individual worksheets (tabs), not individual workbooks. Does anyone know how to do this? It would be great if there were a check box option on the Open File window to select individual files (workbooks) or individual tabs (worksheets in a file). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
individual tabs for subtotaled data | Excel Discussion (Misc queries) | |||
Multiple Files & Tabs Printing Options | Excel Discussion (Misc queries) | |||
Importing XML files into individual worksheets | Excel Discussion (Misc queries) | |||
Protect Individual Tabs Uniquely | Excel Worksheet Functions | |||
Create individual files from a row | Excel Discussion (Misc queries) |