Extracting data with VBA macro button from files in designated folder

[CamBF]

Hi all,

Need an urgent solution to the below please, if possible. It is beyond my VBA capabilities.

I have created a new Excel workbook and inserted a macro button name "Import NC Data".

What I need the macro VBA code to do it to ask me to select / specify a specific folder (folder and location can vary from project to project), from which it will go through each file (there would be many) in that folder and pull data from each file.

The files in the folder are .nc1 files, which can be opened in Excel to display as text in cells.

For each file I need the VBA code to copy the entire cell contents of A5 and paste it in the Excel workbook with the macro, in cell A5.

I then need the VBA code to search column A from that same nc1 file that it just copied A5's contents from for a cell containing only the letters "SI". Once it finds the cell containing "SI", I need the code to copy the cell below it in column A, but only the first 5 characters. Then paste those 5x characters in the Workbook with the macro, in cell B5 (Cell to the right of the previously copy and pasted A5 contents).

The first 5x characters will consist of either spaces and the letter "u" or the letter "o". If any other text character appears in these first 5x characters it would be great if the code could notify the user of which .nc1 file has the different text character and stop the code immediately.

The row that contains "SI" can vary in row number from nc1 file to nc1 file so the code will need to search through all of column A. There will only be one cell in column A that contains "SI".

The VBA code would then look at the next .nc1 file and copy the entire cell contents of A5 and paste it in the Excel workbook with the macro, in cell A6. The code would then do the same as before searching the second .nc1 file for "SI" in column A. Once finding it, copying the first 5x characters of the cell contents below it and pasting them into the Excel workbook with the macro in cell B6.

The code would then move onto the 3rd .nc1 file and so on. Eventually there would be a list in the Workbook with the macro with data in both columns A and B. The contents of each row A and B would be from the same .nc1 file. So if there was data in columns A and B, rows 5-9 then there would have been 5x .nc1 files in the selected folder.

Once the VBA code has scanned all .nc1 files then it will be complete.

Thanks in advance guys, hopefully the above make sense.

 

[Micron]

This is as far as I can take this for now - have to go out. Try this between your button Sub and End Sub lines. If the way that the file dialog presents to you is ok, perahps I can continue later. If not, then some other way of presenting a folder chooser will be needed. For now, it should just print out nc1 file names in a folder.

Dim fd As FileDialog
Dim strPath As String, strFile As String
Dim oFile As file

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
    .Title = "Select project folder"
    .ButtonName = "Select"
    If .Show = -1 Then
        strPath = .SelectedItems(1) & "\*"
    Else
        MsgBox "You canceled folder selection. Process will now terminate."
        Exit Sub
    End If
End With

strFile = Dir(strPath & ".nc1")
Do While Len(strFile) > 0
    Debug.Print strFile
    strFile = Dir
Loop

 

[Micron]

Follow up - you don't need this line: Dim oFile As file (at least not so far).
Also, I think that if there ever could be more than one sheet in the source file, you would have to state what the sheet name would be, and it would have to be the same name in every file, or it's code name would have to be the same in every file. Or the source wb would need to only ever have one sheet in it.