Fetch data from multiple .csv file paste into single sheet

[Deen]

Hi team,

I hope all are doing well,

I have query, in my excel sheet i have a user form in that 10 text box and
10 command button name called"browse" , And i have 1 main spread sheet name
called "inventory"

Question:

I need, once i click 1st browse button windows need to open, browse the
path select the .csv file, same thing is applicable for all 10 "browse button"

Note: in that text box is need to display the browse path.

And there is only one "ok" cmd button, once i click the OK button fetch the
data from multiple .csv file and paste into main spread sheet"inventory"

Please help on this....

Thanks in advance

 

[OssieMac]

Hi Deen,

Hope I have interpreted your request properly.

Copy the following code into a standard module. (See below for more
instructions)

Sub OpenTxtFile(strPath As String)
Dim myTitle As String 'Dalog box title
Dim sFile As String 'Path and csv file name
Dim wbTxt As Workbook

myTitle = "Select the required text file"

With Application.FileDialog(msoFileDialogFilePicker)
.Title = myTitle
.Filters.Clear
.AllowMultiSelect = False
.InitialFileName = strPath
.Filters.Add "Text files", "*.csv", 1
If .Show = False Then
Exit Sub 'If user cancels in dialog box
End If
sFile = .SelectedItems(1)
End With

Workbooks.OpenText Filename:=sFile

Set wbTxt = ActiveWorkbook

wbTxt.Sheets(1).UsedRange.Copy _
Destination:=ThisWorkbook.Sheets("Inventory") _
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

wbTxt.Close

End Sub

Now assuming that you have the file paths in your text boxes, you can call
this code from each of your browse buttons like the following example for the
first button. This calls the sub in the standard module and passes the value
(path) in the textbox to the Sub OpenTxtFile.

Private Sub CommandButton1_Click()
Call OpenTxtFile(Me.TextBox1.Value)
End Sub

The contents of the csv file is copied to Inventory as each csv file is
opened and then the csv file is closed again.

 

[Deen]

Hi OssieMac,

Thanks for your quick response,

I thank to you, its working great...,

According to your coding, i hope no need for 10 text box & 10 cmd buttom.

Sorry for distrub you again, I have 1 more doubt, could you please guide me
if there is any possible at the time select 2 or more .csv files fetch the
data at one click

EG: once i click the browse button windows has got open, with use Ctrl key i
need to select multiple .csv(2 or more csv files) files, and click open
button fetch the data from selected .csv files put into master spread sheet
"inventory"

Could you please help me on this....

Thanks in advance
Deen

 

[OssieMac]

Hi Deen,

Had problems posting this yesterday and it does not appear to have posted.
Will try again.

Call the code similarly to the previous example passing the initial path to
the sub.

Sub OpenMultiTxtFile(strPath As String)
Dim myTitle As String 'Dialog box title
Dim sFile As String 'Selected file name with path
Dim wbTxt As Workbook 'The text (csv) workbook
Dim arrSelected() 'Holds selected files
Dim i As Long

myTitle = "Select the required text files"

With Application.FileDialog(msoFileDialogFilePicker)
.Title = myTitle
.Filters.Clear
.AllowMultiSelect = True
.InitialFileName = strPath
.Filters.Add "Text files", "*.csv", 1
If .Show = False Then
MsgBox "User cancelled at file Open Dialog box"
Exit Sub 'If user cancels in dialog box
End If

'Assign selected file names to an array
ReDim arrSelected(1 To .SelectedItems.Count)
For i = 1 To .SelectedItems.Count
arrSelected(i) = .SelectedItems(i)
Next i
End With

For i = 1 To UBound(arrSelected)
Workbooks.OpenText Filename:=arrSelected(i)

Set wbTxt = ActiveWorkbook

wbTxt.Sheets(1).UsedRange.Copy _
Destination:=ThisWorkbook.Sheets("Inventory") _
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

wbTxt.Close
Next i

End Sub

 

[Deen]

Hi OssieMac,

Thanks a lot, is working great.....


Thanks for your valuable time & support.

Regards,

Deen

 

[Deen]

Hi OssieMac,

I hope you are doing well,

I have query, in my excel sheet i have userform in that have command button
called "export"

Question:

I required, once i click the export button windows need to open, browse the
path select the save location, need to workbook save without macro.

Please help on this....

Thanks in advance
Deen