Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fetch data from multiple .csv file paste into single sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fetch data from multiple .csv file paste into single sheet
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. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fetch data from multiple .csv file paste into single sheet
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" wrote: 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. -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fetch data from multiple .csv file paste into single sheet
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 -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fetch data from multiple .csv file paste into single sheet
Hi OssieMac,
Thanks a lot, is working great..... Thanks for your valuable time & support. Regards, Deen "OssieMac" wrote: 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 -- Regards, OssieMac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export(Save AS) workbook without macro on browse location
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To create a macro to fetch data from excel sheet (2003) using a qu | Excel Programming | |||
Copy and paste data from one sheet to another in a single workbook | Excel Programming | |||
single Email address from one sheet - multiple data from another | Excel Programming | |||
Use of Indirect function to fetch data from another Excel file | Excel Worksheet Functions | |||
Multiple sheets as data for a single sheet | Excel Worksheet Functions |