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" wrote in message oups.com...
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