Import multiple csv files into current workbook as separate sheets

Hi All,

I dont have much expertise in VBA but trying to learn.

I am trying to import multiple csv files into excel. I have a macro
from this forum to import the files one by one. But I want to be able
to select and open the multiple files at once. Also with the macro that
i have, the names of shees are coming like Sheet1, Sheet2 etc. I want
to retain the names of the files for the sheets if possible.

Many thanx for your efforts.

Appending the macro I am using. U can suggest completely different
approach If you think that open dialog box is not best.

Using Excel 2003. Not much expertise in VBA.

Thank You,

Santosh


Sub ImportTextUsingXlDialogOpen()

'\ use this approach to prompt user for text wizard values

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'\ display open file dialog and copy to new (temporary workbook)
'On error resume next - traps for user clicking cancel in the
import dialog
'Error number is 1004 - is so exit the procedure
On Error Resume Next
If Application.Dialogs(xlDialogOpen).Show("*.csv") Then
If Err.Number = 1004 Then
Exit Sub
End If
ActiveSheet.UsedRange.Select '\ select imported text in
temporary workbook
Selection.Copy '\ copy to clipboard
ActiveWorkbook.Close '\ close temporary workbook
Worksheets.Add
Range("A1") = "Dialogs(xlDialogOpen) Method"
Range("A2").Select
ActiveSheet.Paste '\ paste text into your
workbook
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Try this

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


"santosh" <[email protected]> wrote in message news:[email protected]...
> Hi All,
>
> I dont have much expertise in VBA but trying to learn.
>
> I am trying to import multiple csv files into excel. I have a macro
> from this forum to import the files one by one. But I want to be able
> to select and open the multiple files at once. Also with the macro that
> i have, the names of shees are coming like Sheet1, Sheet2 etc. I want
> to retain the names of the files for the sheets if possible.
>
> Many thanx for your efforts.
>
> Appending the macro I am using. U can suggest completely different
> approach If you think that open dialog box is not best.
>
> Using Excel 2003. Not much expertise in VBA.
>
> Thank You,
>
> Santosh
>
>
> Sub ImportTextUsingXlDialogOpen()
>
> '\ use this approach to prompt user for text wizard values
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
>
>
> '\ display open file dialog and copy to new (temporary workbook)
> 'On error resume next - traps for user clicking cancel in the
> import dialog
> 'Error number is 1004 - is so exit the procedure
> On Error Resume Next
> If Application.Dialogs(xlDialogOpen).Show("*.csv") Then
> If Err.Number = 1004 Then
> Exit Sub
> End If
> ActiveSheet.UsedRange.Select '\ select imported text in
> temporary workbook
> Selection.Copy '\ copy to clipboard
> ActiveWorkbook.Close '\ close temporary workbook
> Worksheets.Add
> Range("A1") = "Dialogs(xlDialogOpen) Method"
> Range("A2").Select
> ActiveSheet.Paste '\ paste text into your
> workbook
> End If
>
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
> End Sub
>

Thank you Ron. It works great.

Also Is there any simple way to interactively get the name of the
directory from user ?

Thank a lot.

Santosh

hi Santosh

> Also Is there any simple way to interactively get the name of the
> directory from user ?

See how I do it in this macro
http://www.rondebruin.nl/windowsxpzip.htm#Browse

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Santosh" <[email protected]> wrote in message news:[email protected]...
> Thank you Ron. It works great.
>
> Also Is there any simple way to interactively get the name of the
> directory from user ?
>
> Thank a lot.
>
> Santosh
>

I have a similar macro that is in use to import .csv files into a single workbook as separate tabs. I need to change the static directory and prompt the user to browse for the folder containing the .csv files. Can someone please assist? I am using Excel 2007. Thanks in advance...

HTML Code: 

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Hi Ron,

Even I was also having similar requirement and tried the Macro you provided and it really worked.

However I have faced some issue...

1. The CSV contains some special characters / Chinese Characters and when imported in Excel it looks weird and doesnt gives exact data.

2. I need to import the files onto single tab instead of separate tabs. (There is no hard rule for the naming convention of the CSV files so we can take it as per the Macros requirement for example A1, A2, A3 and so on...basically I have a requirement of importing minimum 12 files in single go based on 1 CSV file per month.

Thanks a Lot...

Gaurav

Hi Arlu, I am new to this forum website and thought of replying on the same. It would be a great help for me if you can guide me on starting a new thread or guiding me on the query i raised.