Renaming an entire folders contents using VBA

[dscott21]

Hi guys, really hope someone can help with this problem as it's driving me insane!

I currently have a VBA code that will open a TIFF file and then copy all the text detected within the TIFF file into a standard text file which is done using OCR as it's a TIFF image file.

This works great but need to take it one step further but I'm struggling at the moment.

Within the TIFF file there is a 6 digit reference number that is contained within the first 2 sentances of the text. I need VBA to rename the filename of the TIFF file with this 6 digit reference number, and then repeat the process for every TIFF file within the folder and also the sub-folders.

Does anyone have any ideas how I can change the code below to do what I need it to do?

Hope you can help, you're my only hope!

Many thanks in advance,
Dave

Here's the code I'm using but please be aware I could only get it to work by selecting 'Microsoft Office Document Imaging 11.0 Type Library' from Tools>References Menu in Excel VBA and I don't think this option is available in any of the more recent versions of Excel (i'm using Excel 2003)

Really hope you can help, I'm so close to getting this nailed.


Sub RenameTIFF()

Dim doc1 As MODI.Document
Dim inputFile As String
Dim strRecText As String
Dim imageCounter As Integer

inputFile = Application.GetOpenFilename
strRecText = ""
Set doc1 = New MODI.Document
doc1.Create (inputFile)
doc1.OCR
For imageCounter = 0 To (doc1.Images.Count - 1)
strRecText = strRecText & doc1.Images(imageCounter).Layout.Text
Next
fnum = FreeFile()
Open "C:\TEMP\testmodi.txt" For Output As fnum
Print #fnum, strRecText
Close #fnum
doc1.Close

End Sub

 

[wigi]

Are you able to pick up the 6 digit reference number using code?

 

[dscott21]

Are you able to pick up the 6 digit reference number using code?

No. I'm able to pick out the entire text within the TIFF file using the above code, but can't figure out how to pick out just the 6 digit reference number contained within the first 2 sentances of the text.

 

[wigi]

Can you show us a clear example of the text file output?

 

[dscott21]

Can you show us a clear example of the text file output?

All the TIFF files are scanned letters which are all the same format (which helps!).

The letters go something like this:

Reference No: 123456

Dear Sir / Madam,

We are writing to you in connection with ..........

Yours Sincerley......

Does that help?

 

[wigi]

Hello

The output aways starts with: "Reference No:" ?

To rename a file, you could use the "Name ... As ..." command in VBA. You can look up the details in the help files. If you can't get it to work, please post an update in this topic.

 

[dscott21]

Hello

The output aways starts with: "Reference No:" ?

To rename a file, you could use the "Name ... As ..." command in VBA. You can look up the details in the help files. If you can't get it to work, please post an update in this topic.

Yes, most of them start with: "Reference No:", however I'm not sure how to get the 'strRecText' to pickup the 6 digit reference number rather than the entire text content of the TIFF file.

 

[wigi]

Hello

Give this a try, I cannot test it since I have not all references and files.

Sub RenameTIFF()

    Dim doc1 As MODI.Document
    Dim inputFile As String
    Dim strRecText As String
    Dim imageCounter As Integer
    Dim s6DigitNumber As String, v6DigitNumber As Variant
    
    inputFile = Application.GetOpenFilename
    strRecText = ""
    Set doc1 = New MODI.Document
    doc1.Create (inputFile)
    doc1.OCR
    v6DigitNumber = Split(Trim(doc1.Images(0).Layout.Text))
    s6DigitNumber = v6DigitNumber(UBound(v6DigitNumber))
    For imageCounter = 0 To (doc1.Images.Count - 1)
        strRecText = strRecText & Trim(doc1.Images(imageCounter).Layout.Text)
    Next
    fnum = FreeFile()
    Open "C:\TEMP\testmodi.txt" For Output As fnum
    Print #fnum, strRecText
    Close #fnum
    doc1.Close
    MsgBox s6DigitNumber

End Sub

What is picked up from the file, is everything after the last space on the first line of the file.