ManuAdam
10-27-2013, 11:24 PM
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)
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)