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.

 

[CamBF]

Hi Micron,

Thanks the dialog box is what I was after, running the VBA it doesn't appear to do anything else yet. I couldn't see where it was printing out the file name to. There will only ever be one sheet in the .nc1 files, the worksheets in the .nc1 files will always be named different from .nc1 file to .nc1 file.

See link to some example .nc1 files, if it works: 3 Items

If it helps, I have managed to find the below which does work to go through the folder (however want to use your dialog box to determine the folder location each time the macro is run) and populate the A5 values (for each .nc1 file) in Column A of the worksheet with the macro. I just need to have it also search column A of each of the .nc1 files for "SI", copy the first 5 characters of the cell below "SI" and paste the values in column B of the macro's workbook.

Sub Import_NC_Data()

  Dim sourceFolder As String
    Dim sourceFiles As Object
    Dim sourceFile As Object
    Dim wbSource As Workbook
    Dim wsDestination As Worksheet
    Dim destinationRow As Long
    
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    ' Set the path to the source folder modify accordingly
    sourceFolder = "C:\***************"
    
    ' Set the destination worksheet modify sheet name accordingly
    Set wsDestination = ThisWorkbook.Worksheets("Sheet1")
    
    ' Initialize the destination row
    destinationRow = 1
    
    ' Create a FileSystemObject to work with files in the folder
    Set sourceFiles = CreateObject("Scripting.FileSystemObject").GetFolder(sourceFolder).Files
    
    ' Loop through each file in the folder
    For Each sourceFile In sourceFiles
        ' Check if the file is an Excel file
        If sourceFile.Name Like "*.nc1" Then
            ' Open the source workbook
            Set wbSource = Workbooks.Open(sourceFile.Path)
            
            ' Copy the values
            wbSource.Worksheets(1).Range("A5").Copy
            
            ' Paste the values to the destination worksheet
            wsDestination.Range("A" & destinationRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
          
            ' Update the destination row for the next set of values
            destinationRow = destinationRow + 1
            
            ' Close the source workbook without saving changes
            wbSource.Close SaveChanges:=False
        End If
        
    Next sourceFile
    
    ' Clear the clipboard
    Application.CutCopyMode = False
    
    ' Display a message when the copying is complete
    'MsgBox "Copying customer information from files complete."

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Micron]

tried to send you a pm from my phone but seems not possible for some reason so I'll just post that I just got home from my 2nd trip state side so will try to look at that tomorrow.

 

[CamBF]

Ok no worries, thank Micron

 

[Micron]

OK, have reviewed the requirements and made notes. If you're ok with answering questions, then I'll do what I can. Reason for so many questions is that I prefer to nail it the first time if I can, rather than repeatedly having to "fix" what isn't working as needed. Besides, I don't want to mess up your data or the process hence the questions and advice to run provided code on a copy of your workbook.

The requirements as I understand them (and one or two suggestions):
src = source; x represents a row number

- get folder path from user (using file dialog folder picker)
- copy from first .nc1 file (src), range A5 to target wb (A5)
- find "SI" in src col. A, offset one row in col A, copy five leftmost characters (range "A" & x)
..... - I suspect the test is "does that string contain a 'u' or 'o' ?". If not, action needs to be taken. More on that below.
..... - if u or o is found, copy string to src range(B5)
- advance to next .nc1 file, rinse and repeat

Here's what I suggest:
- get folder path as above
- In If ... Then block, look for "SI" in 1st file, col(A). If not found, exit this If block, move to next file. Or is this guaranteed to never happen?
- If found, offset one row in col(A). Assign Left(offset cell value,5) to a variable (e.g. strValue) and test for "u" and "o"
- - In nested If block, if u or o not found, add complete file path to an array or collection. Exit this if block to move to next file. So far, nothing has been copied.
When u or o is found:
- make x = 5
- copy src(Ax) to wb(Ax)
- copy strValue to wb(Bx)
- increment x by 1
- set a helper column to a value*
- if .nc1 files have to be opened to copy from, close current file, loop to next file and repeat.

* My suggestion is don't stop the process when u or o is not found then start it all over again from the beginning. If you said how many files there can be I missed it, but imagine 100 files and 5 failures and repeating all of this 5 times on 100 files. Even if there is no failure, your button click will repeat the entire process on any folder should anyone not realize a particular folder was already done. So in a helper column of wb (does not have to be visible) have code inject a value on row x or y. Thus the above would need a test for helper value, either on the row that contains SI or row that the value was copied from. Or some other row if you think that would be better. If value is found the idea would be to skip the copy/paste as it would already have been done. All that would be done before looking for SI.

Before the process terminates entirely, do something with the array/collection of file paths where u or o is not found and present to user somehow. Now user fixes those, repeats process for same folder and code skips the rows having the helper value.

I'll await your comments. Note that this is all just a concept in my head and can't promise I can bring it to fruition as suggested. I probably spent an hour just getting my head around the ideas and making this post and I may have overlooked something.

 

[CamBF]

Hi Micron,

See below my comments:



The requirements as I understand them (and one or two suggestions):
src = source; x represents a row number

- get folder path from user (using file dialog folder picker) Yes
- copy from first .nc1 file (src), range A5 to target wb (A5) Yes
- find "SI" in src col. A, offset one row in col A, copy five leftmost characters (range "A" & x) Correct, will nearly always (99% of cases) be one row below "SI"cell. I am wanting the code to pick up the "o" or the "u" and display it in column B of the macro workbook (I figured that doing the first 5 characters would always pick these up), that determines what side of the member the part marking is on. This allows me to see which files / members need the marking changed after the code is run. If the code cant pickup "u" or "o", I was thinking that the cell for that file in column B would just appear blank. That way the cell value in the B column is always taken from the exact .nc1 file that the cell value to the left in column A is taken from.
..... - I suspect the test is "does that string contain a 'u' or 'o' ?". If not, action needs to be taken. More on that below. Yes
..... - if u or o is found, copy string to src range(B5) Yes
- advance to next .nc1 file, rinse and repeat Yes

Here's what I suggest:
- get folder path as above Yes
- In If ... Then block, look for "SI" in 1st file, col(A). If not found, exit this If block, move to next file. Or is this guaranteed to never happen? I would like it still to scan for "SI" in every file, if it cant find "SI" then display a blank cell in the macro workbook column B.
For every file in the folder I would like the cell A5 to be copied to the macro workbook column A regardless. Column A5 will always contain information I want to see and to check that it has been scanned by the code. I also want to be able to copy this information into other documents so need to have every .nc1 file in the folder listed as a row.
- If found, offset one row in col(A). Assign Left(offset cell value,5) to a variable (e.g. strValue) and test for "u" and "o" Offset one row down in column A (1 cell below "SI")
- - In nested If block, if u or o not found, add complete file path to an array or collection. Exit this if block to move to next file. So far, nothing has been copied. If "o" or "u" is not found, display a blank cell in macro workbook column B.
When u or o is found: Regardless of if "u" or "o" is found, please copy at least A5 cell from every .nc1 file to the macro workbook.
- make x = 5
- copy src(Ax) to wb(Ax)
- copy strValue to wb(Bx)
- increment x by 1
- set a helper column to a value*
- if .nc1 files have to be opened to copy from, close current file, loop to next file and repeat.

* My suggestion is don't stop the process when u or o is not found then start it all over again from the beginning. If you said how many files there can be I missed it, but imagine 100 files and 5 failures and repeating all of this 5 times on 100 files. Even if there is no failure, your button click will repeat the entire process on any folder should anyone not realize a particular folder was already done. So in a helper column of wb (does not have to be visible) have code inject a value on row x or y. Thus the above would need a test for helper value, either on the row that contains SI or row that the value was copied from. Or some other row if you think that would be better. If value is found the idea would be to skip the copy/paste as it would already have been done. All that would be done before looking for SI. I wouldn't want the code to start again if "u" or "o" is not found either, rather just display a blank in column B of the macro workbook and move onto the next .nc1 file.

Before the process terminates entirely, do something with the array/collection of file paths where u or o is not found and present to user somehow. Now user fixes those, repeats process for same folder and code skips the rows having the helper value. This wouldn't be necessary as there will be blank cells in column B where "u" or "o" was not found, I plan to set the workbook up with conditional formatting at a later date to visually make the difference in the cell values more apparent at a glance. It would be helpful for the code at the very start to wipe the cell values (not formatting) of columns A and B so that the macro button can be repeatedly pressed knowing that it will only display the latest information after each time it is run.

 

[Micron]

I downloaded your files and am organizing all that info into something I can follow.
I notice that in each file, SI is followed by a value where the 1st character is o. It would be nice if code could just look at 1st character and didn't have to look for o or u anywhere in the 1st 5 characters. I don't suppose that would be ok?

 

[CamBF]

Hi Micron,

No sorry we cant change the way the .nc1 file displays the data. It is done a specific way to work with steel processing machines. It has to stay as it is.

The code has to search the cell / row below SI for the "u" or "o". That's the information we need to pick up on with the code (along with A5) and what needs to be displayed in column B of the macro worksheet.

Thanks Micron