Pulling one values from CSV files? 1

[Asspin]

Ok folks, I am not really sure where to go with this, all my VBA has so far been confined to manipulating one document. I have been asked to make a report from 40 CSV files and compile them into one Excel doc. I only need a couple values out of each file.

Here is my code so far, I have been trying some things with setting the FileSystemObject, but it is too ugly to include.

    Dim strDate As String, strFolder As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    strDate = InputBox("Please enter a day to generate data. (MM/DD/YYYY)", "Select Date", Date - 1)
    If LenB(strDate) = 0 Then Exit Sub
    strFolder = "\\Opr.statefarm.org\dfs\OC\20\WORKGROUP\GRLYOPEN\LRC\Planning\Data\" & Year(strDate) & "-" & Month(strDate) & "-" & Day(strDate) & "\"
    If fso.FolderExists(strFolder) Then
        'Do Stuff
    Else
        MsgBox "There is no data for that date!"
    End If

The files are all in order with the name 277.csv, 278.csv, 279.csv, etc. I am needing to grab row 7, values 1 and 10 from each CSV. I am really at a loss as to where to go with this. Any help would be awesome!

Dan

 

[Bong]

Why not add a temporary sheet, import the CSV data, extract what you need, and blow that sheet away (and repeat)?

_________________
Bob Rashkin

 

[SkipVought]

it might be simpler to add the Data > Import External Data > IMPORT... querytable, and just loop thru the NAMES, 277, 278..., changing the Connection property.

Here's the drill.

Add a QueryTable using Data > Import External Data.... for the FIRST .csv file.

Turn on your macro recorder and record EDITING the querytable. The recorded code can be used to modify the Connection property of the QueryTable object.

Then it's a simple loop to substitute the file number in the Connection string, refersh the QueryTable, retrieve the values in row 7.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}