Import file name / delimited text to column / delete useless text with vba, marco. RRS feed

  • Question

  • Hi there,

    I have a very complicated task to do and I would love to make it happen. I have been looking for hours on the internet for my specific problem. I found about 50% of it but can't figure how to put them together and make ONE vba function.

    Here's what I need: Import in excel several filenames from a specific folder, split the name of the file into 2 seperated columns and delete the last 4 digit. I would also need this function to update itself in my excel sheet everytime a new file gets dropped in that same folder.

    Here's an example of the files located in my folder. They are pdf drawing from a construction site.

    2323234-WQW-DDD REV 1V1.PDF
    2323235-WQW-DDD REV 1V1.PDF
    2323236-WQW-DDD REV 1V1.PDF
    2323237-WQW-DDD REV 1V1.PDF
    2323238-WQW-DDD REV 1V1.PDF
    2323239-WQW-DDD REV 1V1.PDF
    2323230-WQW-DDD REV 1V1.PDF
    2323240-WQW-DDD REV 1V1.PDF
    2323241-WQW-DDD REV 1V1.PDF


    I want to use a macro to import them, split the revision (REV 1V1)  in another column and delete the extension. Then I also need to create and 3rd column with the first 11 digit.

    It would look something like this.

    Drawing # REVISION Line #
    2323234-WQW-DDD  REV 1V1 2323234-WQW 
    2323235-WQW-DDD  REV 1V1 2323235-WQW
    2323236-WQW-DDD  REV 1V1 2323236-WQW
    2323237-WQW-DDD  REV 1V1 2323237-WQW
    2323238-WQW-DDD  REV 1V1 2323238-WQW
    2323239-WQW-DDD  REV 1V1 2323239-WQW
    2323230-WQW-DDD  REV 1V1 2323230-WQW
    2323240-WQW-DDD  REV 1V1 2323240-WQW
    2323241-WQW-DDD REV 1V1 2323241-WQW

    I found how to import filename into excel and right now I use a =LEFT(A2, LEN(A2)-4) function to get rid off the extension. I manually split the text to get the different columns that I need. The problem is that I need to update this file everyday and new files are coming in and I have thousands of file like this.

    I don't know how to make it in ONE VBA action.

    Thank you so much !

    Thursday, April 17, 2014 3:53 PM

All replies

  • Assuming the filenames all follow the same pattern as shown in your example, you could create a sub to process the filename e.g.

    Sub SplitFilename(strFilename As String)
    Dim strName() As String
    Dim xlSheet As Worksheet
    Dim strRev As String
    Dim strDraw() As String
    Dim NextRow As Long
    Dim i As Long
        Set xlSheet = ActiveSheet
        NextRow = xlSheet.UsedRange.Rows(xlSheet.UsedRange.Rows.Count).Row + 1
        strName = Split(Left(strFilename, Len(strFilename) - 4), Chr(32))
        strDraw = Split(strName(0), "-")
        xlSheet.Cells(NextRow, 1) = strName(0)
        xlSheet.Cells(NextRow, 2) = strName(1) & Chr(32) & strName(2) & Chr(32) & strDraw(0) & "-" & strDraw(1)
    End Sub

    Then call it from your existing macro for each name e.g.

    SplitFilename "2323234-WQW-DDD REV 1V1.PDF"

    If you need any help in grabbing the filenames then post back.

    Graham Mayor - Word MVP

    • Proposed as answer by ryguy72 Friday, May 2, 2014 8:39 PM
    Friday, April 18, 2014 2:46 PM