Copy filenames from a folder and fetch data in respective rows from those files

[ManuAdam]

I am using a module to fetch filenames from a folder.

Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub

Now I have a few queries.
Scenario: What I am trying to do and my target:
I get realtime data in csv files from my broker terminal. The moment market starts, these csv files (with respective stock names) are created in a particular folder, named data. All these CSV files have three columns: TIME PRICE VOLUME.
Files names are usually like this: FUTCOM-ALUMINIUM-29NOV2013_MCX113928.csv
I am fetching these file names in first column of my myfile.xlsm. I will further feed this realtime data to my charting software.

Query 1: When I run this module it asks (searches) for folder. Can I give the full path in module so that it automatically goes that folder instead of searching for it. Where to insert folder path.

Query 2: The module fetches full file name. I want it to be trimmed like this: From FUTCOM-ALUMINIUM-29NOV2013_MCX113928.csv to FUTCOM-ALUMINIUM-29NOV2013.

Query3: How to further fetch data from three columns TIME PRICE VOLUME from each csv files into my myfile.xlsm in the row of their respective names. Data from just the last row of each csv files (as they are dynamically changing in real time).

I will be very grateful to you if you could find some of your precious time to resolve them. Thanks & Regards. Manu Adam (New Delhi, India)

[patel]

for the first 2 questions

Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\test" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = Left(xFname$, Len(xFname$) - 4)
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub
for the last i need a sample csv file

[ManuAdam]

Dear Sir, Thanks for your reply and help. I am providing you the attachment of sample csv files here which you can download by clicking at the links. There is some problem here. You said that the present change resolves my first 2 queries. What I see that you have changed the line:ActiveCell.Offset(xRow) = Left(xFname$, Len(xFname$) - 4).I changed - 4 to -14 in order to remove the _MCX113928.csv prefix from the file names. But whenever I put your suggested line, vb editor yellows that line and shows bug problem. Why is that so?However it does trim the name and hence does the work.Where is answer to My first query to give default data path, so that I need not open the folder every time I open the file and micro is run.Where to give full path for the folder? Please direct me.About the Query3: Please download the sample files from here as I Have attached it hereAs such file is like that: Ist col (Date) , 2nd col(Price), 3rd col (Volume)20131022.100035 114 020131022.100035 114 020131022.100028 114 020131022.100026 114 020131022.100028 114 020131022.100029 114 0This data I want to feed into myfile.xlsm in order to further feed this real time data into charting software. Thanks again for your kind help.

[patel]

attach please your xlsm file with code, data and desired result

[ManuAdam]

attach please your xlsm file with code, data and desired result

Dear Mr Patel, As such my xlsm file has nothing more than this module (which you so kindly helped me to modify) that I ran to fetch the filename in the first column A of my xlsm file. Now after getting the filenames, my next step is to get respective data from these csv files in subsequent three columns B C & D. However, acting on the principle that Guru is always right and never to argue with your Guru, I am hereby attaching that xlsm file. Please look at it and guide me further. Thanks & Regards

[patel]

I can not see desired result as requested

[ManuAdam]

I can not see desired result as requested

What do you mean by this? I have written clearly in my first question: Query3: How to further fetch data from the last row three columns TIME PRICE VOLUME from each csv files into my myfile RTtest1.xlsm in the row of their respective names. I got the names in first column with the help of above module. Now I want to get Data from just the last row of each csv files (as they are dynamically changing in real time).
Lol. How can you see the desired result, when I have not yet got it. For this only I am seeking help here.