Extracting certain cells from excel files in a specified directory

Question

I am a VBA novice and I am trying to write a code that will copy the following data from multiple csv files all stored in the same directory.

I need it to open each csv file

Check IF Row 8 in columns H through CA for any cells that have the value ="TotalLMP" (Sample: Cell H8="TotalLMP")

THEN copy the value from both Row 7 and Row 19 of any columns that have that value ="TotalLMP" in Row 8 in two new columns (Sample: SINCE H8="TotalLMP", COPY H7="100" AS COLUMN A, COPY H19 = "26.437" AS COLUMN B)

THEN copy the value from cell $A$9 in a third column (Sample: COPY A9="20100101" AS COLUMN C")

after finishing loop through each csv file close and go to next

Then in the new active worksheet in the blank excel file would store each value as follows:

.......A .............. B ................ C

1 .. 100 .... 26.437 .... 20100101

2 .. 200 .... 26.585 .... 20100101

If you have any existing code you should include that in your question - even if it doesn't work. Just posting a requirement, without any indication you've tried to solve it yourself, often leads to questions being closed. — Tim Williams, Jul 02 '13 at 16:15
+1 to Tim's comment above. We expect you to have tried something .... — brettdj, Jul 03 '13 at 11:40

score 3 · Answer 1 · answered Jul 02 '13 at 16:23

Let me help you with the CSV looping for now since this is rather hard for a beginner. I'm sure you will figure out how to test for a value in row 8. If not, you can always ask for more help!

In order to do so, you will have to use the Microsoft Scripting Runtime.

I suggest placing all of the csv file you want to open in the same directory, and only those to avoid potential problems.

Open a new workbook and go to the VBE (ALT + F11). Create a new module. Click in this new module, then go to Tools > References> Microsoft Scripting Runtime. This will let it know it will have to use that module and its objects.

Save the workbook as an macro-enabled workbook (.xls or .xslm for newer versions) in the same directory as your CSV (or somewhere else...)

Then start coding:

Sub Import_all_Csv()
' Reference Needed: Microsoft Scripting Runtime

' Dim some pointers to know what objects you will be manipulating thereafter
Dim MyWs, CSV As Worksheet
Set MyWs = ActiveSheet ' Meaning you will have to run the macro from the spreadsheet you want to export to. Feel free to replace that
Dim wbCSV As Workbook

' Those are the objects that belong to the reference Microsoft Scripting Runtime
Dim oFSO As FileSystemObject
Dim oFld As Folder
Dim oFile As File

Dim File As String

' Initialize the FileSystemObject
Set oFSO = New FileSystemObject

' That will only work on windows so I'm adding an error handler to ignore it if need be
On Error Resume Next
ChDir ThisWorkbook.Path
On Error GoTo 0 ' I'm asking VBA to throw an error now

' Dialog box to select the first csv file (this will let you choose another directory everytime)
File = Application.GetOpenFilename("Comma Separated Values File (*.csv*), *.csv*")
If File = "False" Then
    Exit Sub ' Quit the macro if user canceled
Else
    ' Else get the path of the parent folder of that file
    Set oFld = oFSO.GetFolder(oFSO.GetParentFolderName(File))
End If

' Go through each file in that folder
For Each oFile In oFld.Files

    ' Only open the CSV files
    If oFile.Type = "Microsoft Excel Comma Separated Values File" Then
        ' Open it and set the first sheet (There is only one anyway)
        Set wbCSV = Workbooks.Open(oFile)
        Set CSV = wbCSV.Sheets(1)
        ' ============================
        ' Do what you want to do Here

        ' THIS IS A PLACEHOLDER
        ' Example to copy value of H8 in the CSV file to A2 the destination worksheet so you can see how to point to the correct cells in both files
        MyWs.cells(1,2).value = wCSV.cells(8,8).value

        ' End of what you want to do
        ' ============================

        ' Close the CSV file without savings changes before going through the next one
        wbCSV.Close False

    End If

Next oFile

End Sub

I hope this helps! Good luck learning more VBA!

Best, Julien

Using `Dir` with a wildcard is much more efficient that testing the FileType of every file in the directory. See http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba/10382861#10382861 — brettdj, Jul 03 '13 at 11:39
Oh, that seems pretty cool. I'll definitely look into it (works on Mac?). Thanks! — Julien Marrec, Jul 03 '13 at 11:42
Me neither but I've had problems in the past with people using my macros on Mac and it would crash at ChDir, hence the disabling of the error handling for that specific line — Julien Marrec, Jul 03 '13 at 11:55

Extracting certain cells from excel files in a specified directory

1 Answers1