help with vba

[22newbie]

I have a code that pulls all the files from a folder to the spreadsheet. It works perfectly fine. However, I would like to add a column the first 2 letters of the filenames so I can keep track of where the data is from.
Here is the code. Hopefully someone can help

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook
    Set desWS = ThisWorkbook.Sheets("Master")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\test\Desktop\New folder\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With srcWB.Sheets("Sheet2")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("A2:D" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
        srcWB.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Micron]

Since you're pasting the paths in a batch I think you'll have to get the characters by either dragging a formula down the desired column. If there is an actual formula that will capture the 2 characters, I wouldn't be able to help with that. Another option would be to drag down a formula/expression that calls a vba procedure to extract the characters. As a start, if that procedure used InstrRev and Mid it could work. E.g.

mid("C:\Users\test\Desktop\New folder\TEST.xlsm",instrrev("C:\Users\test\Desktop\New folder\TEST.xlsm","\")+1,2)

returns TE

 

[22newbie]

Since you're pasting the paths in a batch I think you'll have to get the characters by either dragging a formula down the desired column. If there is an actual formula that will capture the 2 characters, I wouldn't be able to help with that. Another option would be to drag down a formula/expression that calls a vba procedure to extract the characters. As a start, if that procedure used InstrRev and Mid it could work. E.g.

mid("C:\Users\test\Desktop\New folder\TEST.xlsm",instrrev("C:\Users\test\Desktop\New folder\TEST.xlsm","\")+1,2)

returns TE

Thank you so much for your response, Micron!
I'm not sure if using the formula after compiling will be helpful because I won't know which set of data is from which file. You know what I mean? It will be a lot of manual work ..... (thinking)

 

[22newbie]

Since you're pasting the paths in a batch I think you'll have to get the characters by either dragging a formula down the desired column. If there is an actual formula that will capture the 2 characters, I wouldn't be able to help with that. Another option would be to drag down a formula/expression that calls a vba procedure to extract the characters. As a start, if that procedure used InstrRev and Mid it could work. E.g.

mid("C:\Users\test\Desktop\New folder\TEST.xlsm",instrrev("C:\Users\test\Desktop\New folder\TEST.xlsm","\")+1,2)

returns TE

Sorry, I am not an expert in VBA. Where do I insert 'mid("C:\Users\test\Desktop\New folder\TEST.xlsm",instrrev("C:\Users\test\Desktop\New folder\TEST.xlsm","\")+1,2)' in the procedure?

 

[Micron]

I would like to add a column the first 2 letters of the filenames so I can keep track of where the data is from.

I guess I didn't and still don't know what you mean. What I suggested seems to do exactly what that says? Not that I can see how 1st 2 letters of a file name tells you much about where it came from, which I figured was right there on the sheet. Perhaps you code doesn't do what I thought it does - paste file paths from a directory into a sheet.
EDIT - this code, in a standard module

Function extractTwo(strIn As String) As String

extractTwo = Mid(strIn, InStrRev(strIn, "\") + 1, 2)

End Function

and called by entering extractTwo(A2) into cell formula bar (start with whatever column and row address should be) and hitting enter should work. If it does you can drag it down the column.

 

[22newbie]

I guess I didn't and still don't know what you mean. What I suggested seems to do exactly what that says? Not that I can see how 1st 2 letters of a file name tells you much about where it came from, which I figured was right there on the sheet. Perhaps you code doesn't do what I thought it does - paste file paths from a directory into a sheet.
EDIT - this code, in a standard module

Function extractTwo(strIn As String) As String

extractTwo = Mid(strIn, InStrRev(strIn, "\") + 1, 2)

End Function

and called by entering extractTwo(A2) into cell formula bar (start with whatever column and row address should be) and hitting enter should work. If it does you can drag it down the column.

Thank you again. I'll try. I'm not sure if I understood how to do as well using the call function as I am new to vba

 

[22newbie]

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, srcWB As Workbook
  
    Set desWS = ThisWorkbook.Sheets("Master")
    Dim LastRow As Long
    Const strPath As String = "C:\Users\TEST\Desktop\New folder\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With srcWB.Sheets("Sheet2")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Range("A2:D" & LastRow).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
        srcWB.Close False
        strExtension = Dir
        


    Loop
    Application.ScreenUpdating = True
    

End Sub


Function extractTwo(strIn As String) As String

extractTwo = Mid("C:\Users\TEST\Desktop\New folder\test.xlsm", InStrRev("C:\Users\TEST\Desktop\New folder\test.xlsm", "\") + 1, 2)
End Function

This is what I have. I know I sound stupid - How do I get it to work? >_<

 

[Micron]

I'm a very literal person and struggle with interpretations and vagueness sometimes. My fault.
This is one of those times. I have no idea what "it" is - especially when you pose that question and add your code to it. You said that code was working, yes? Then "it" works?

Are you asking me how to implement what I suggested?

 

[22newbie]

I'm a very literal person and struggle with interpretations and vagueness sometimes. My fault.
This is one of those times. I have no idea what "it" is - especially when you pose that question and add your code to it. You said that code was working, yes? Then "it" works?

Are you asking me how to implement what I suggested?

I am so sorry. My bad. I am not good at explaining since I am not an expert in VBA.

So... the first part is working - i.e. pulling all the data into a worksheet called 'Master'
However, I don't seem to know how to get the Function part to work (what you suggested) using the call function. Where do I add that to the existing 'Sub Copyrange' code?

 

[Micron]

open visual basic editor (Alt+F11) in your active workbook, go to the insert menu, and select module. This will insert a standard module into your current project.

They look like this (Module1, 10, 11 etc.)

Paste the code I gave you into your module (or simply add it to an existing module. If you add to existing, it must not be a module behind a sheet or userform, or anything else for that matter.

I entered the function call into A1. If you drag it down like any other formula, it should automatically refer to the next row in the column for each value in A. If there is no value, B cell should be blank. Enter into whatever column you like, as long as A1 is replaced with the cell address you need.