VBA extract multiple file names and file paths from a string?

[rex.withers]

Hi everyone, wondering if anyone has some help on this I'm trying to extract multiple file names and file paths from a string. In the sample string below (from an imported email that has an auto attachment save and link add-on) detail in the image below because the forum wouldn't allow my post due to it looking like spam??

Thanks!

 

[jdraw]

PLease include some sample records in a post--mock ups if you want.

 

[rex.withers]

PLease include some sample records in a post--mock ups if you want.

Just the pure VBA is all that I need for example button 1 below (cmdExtractFiles) needs to extract the file names from A (txtSubject) and put it in B (txtFiles) once I the the code that does the extraction I can perhaps build on it in a sub table with links etc but I cant get past the first step (THANKS for your help)

 

[jdraw]

I understand but it's a bit of test data --not a picture of data--- that is needed to set it up.

Are the filenames structured in a similar pattern
File:///D:\EmailAttachments\2023\..................docx/png/jpg/pdf

 

[rex.withers]

Here you go, attached
Yes always always the same path structure (as above) [Note I cant post any sort of path in the text here it gets blocked]

Thanks

 

[jdraw]

It's late here.
Here's a mockup that may get you started. It only deals with 1 file per email at the moment.
Run the function in module1. Use f8 to step through the code. You'll need to check that there are startPosn and endposn values, if either or both are 0 then bypass/go to next record.
Good luck.

 

[KitaYama]

Try this on your button's onclick event:

    Dim Lines As Variant
    Dim itm As Variant
    Dim result As String

    Lines = Split(txtJobActioncompleted, vbCrLf)
    For Each itm In Lines
        If InStr(itm, "Attachment saved to file:///") > 0 Then
            result = result & "," & Replace(itm, "Attachment saved to file:///", "")
        End If
    Next

    result = Right(result, Len(result) - 1)
    txtFiles = Replace(result, ",", vbCrLf)

 

[ebs17]

With regular expressions and their Execute method you can read all paths in one fell swoop. With the very similar path names, creating the necessary pattern is not that difficult.

 

[rex.withers]

Try this on your button's onclick event:

    Dim Lines As Variant
...
[/QUOTE]
Thanks KitaYama, interesting never though of doing it like that

 

[rex.withers]

It's late here.
Here's a mockup that may get you started. It only deals with 1 file per email at the moment.
Run the function in module1. Use f8 to step through the code. You'll need to check that there are startPosn and endposn values, if either or both are 0 then bypass/go to next record.
Good luck.

Thanks JDraw, that is great the way that you did it allows for it to be done as part of the import process very smart better than my idea. How would you amend that to handle multi files in the same string?

 

[rex.withers]

With regular expressions and their Execute method you can read all paths in one fell swoop. With the very similar path names, creating the necessary pattern is not that difficult.

Ebs17 Do you have any example code for that I have never used regular expressions, brief investigation looked... tricky... (gulp)

 

[jdraw]

Thanks JDraw, that is great the way that you did it allows for it to be done as part of the import process very smart better than my idea. How would you amend that to handle multi files in the same string?

My mockup was to show the use of Instr and Mid when parsing generally. I thought that was your basic concern.
I like KitaYama's approach and, if your data wasn't so organized, you might have to parse the array elements to remove extraneous characters.

 

[ebs17]

any example code for that

warning and not allow certain words being entered

i would like to be able to stop people entering certain words within a sentence in the text field MaterialPUR. and warning them if they try to. the words for example are. drw drg drawing w room see screenshot below as an example thanks steve

www.access-programmers.co.uk

Is that enough for a first impression?

 

[jdraw]

Rex,

Here is a sample, based on previous, that uses KitaYama's split (sort of) and additional parsing.
It demos handling multiple file references within each record.

' ----------------------------------------------------------------
' Procedure Name: parseEmail4File
' Purpose: Custom proc to parse text an isolate specific file names and path
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
' Date: 11-Aug-23

' Note: Element(0) will not contain the pattern since it is the separating value
' Other elements will contain 1 file with some extension
' Only extensions in fExt will be parsed
' ----------------------------------------------------------------

 

[rex.withers]

Thanks very much everyone KitaYama's solution was perfect