Solved: Importing jpg plots based on names in ASCII text file.

[RockMechMark]

I am requesting help with the following problem. First, is it possible to do without too muchdifficulty? Second, can someone provideme some initial code?
Objective: I need tosave time by automating import of jpgs into Word. I have several jpg pictures or plots ofcalculations. These plots may reside inmultiple folders. I want to be able toexecute a macro that imports these plots in a certain order into a Worddocument. Here is the general algorithmI am thinking of.
1. Once the blank Word document is open, I wouldexecute this macro.
2. Set margins of the document.
3. Read in the filename of an ASCII file containingthe path and filenames of the jpg plots in order of desired import (andappearance in the Word file), preferably one per line; OR this ASCII file may always have the samename.
4. For each jpg path\filename listed in the ASCIIfile (so the number of jpg files to import will have to be determined fromnumber of lines in the ASCII file).
a. Import the jpg file.
b. Execute a subroutine (in a macro that I alreadyhave and is functional) which crops the jpg and resizes it (all jpgs have thesame dimension, so this works).
c. If needed, position the cursor just after theobject just imported and resized in Word.
My intent is that this macro places two plots on the samepage. The main obstacle to me is whetheran ASCII file can be opened and its contents read, line by line, into an arrayfor later import, or read sequentially and the import executed and the jpgcropped and resized after it reads each line. The length of each line would have to be rather long…probably a maximumof 80 to 100 characters to accommodate the path and filename of each jpg.

[RockMechMark]

Ladies and Gentlemen,

No takers yet. Let me break down the algorithm into subtasks and I will write some starting code. I ask your help with that to clear up problems, suggest alternatives, and correct errors.

So let me suggest code as a starting point for setting the paper margins and opening and reading an ASCII file that contains the names of files (I call them plots) to be imported later. I want to put these names in an array variable and determine the number of plots or lines in that ASCII file so that I can later set up a Do Loop to import them separately.

Here is the code:

SubImportPlots()
'
'
' Declarations
Dim PlotFileName As String
Dim PlotNames(50) As String
Dim NumOfImports As Integer
'
' Set document margins
PageSetup.TopMargin = 0.75
PageSetup.BottomMargin = 0.75
PageSetup.LeftMargin = 1.
PageSetup.LeftMargin = 1.
'
' Read in path and filename of ASCII filecontaining names of plots to import
' This string has to be at least 80characters long. I don’t know whetherthat
' length is not possible or if there are any such constraintsto string variables.
Dim PlotFileName As String
Dim PlotNames(50) As String
Dim NumOfImports As Integer
'
' Open PlotFileName
Open PlotFileName For Input As #1
Cnt = 1
Do While Cnt < 51
Get #1, Cnt, PlotNames(Cnt)
' Here, how do I distinguish that theremay be x lines, which is short of 50 in
' number?
If (at end of file…for example, if PlotNames(Cnt)== null) ' I am unsure
' how to do this.
NumOfImports = Cnt – 1
Cnt = 51
Else
NumOfImports = Cnt
End If
Loop
'
End Sub

[macropod]

Here's one way, using INCLUDEPICTURE fields so that the plots in the document remain linked to the original files (it's easy enough to break the links if you don't want that). Other than that, simply edit the path & name in StrFile to point to your plot file.
Sub Demo()
Dim wdDoc As Document, StrFile As String, StrPlots As String, i As Long
StrFile = "C:\Users\" & Environ("UserName") & "\Documents\PlotFile.txt"
Set wdDoc = Documents.Open(FileName:=StrFile, ReadOnly:=True, _
ConfirmConversions:=False, AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, Encoding:=msoEncodingUTF8)
StrPlots = Replace(wdDoc.Range.Text, "\", "/")
wdDoc.Close
Set wdDoc = Nothing
With ActiveDocument
For i = 0 To UBound(Split(StrPlots, vbCr)) - 1
.Range.InsertAfter vbCr
.Fields.Add Range:=.Range.Characters.Last, Type:=wdFieldEmpty, _
Text:="INCLUDEPICTURE """ & Split(StrPlots, vbCr)(i) & "", _
Preserveformatting:=False
Next
Next
End Sub
PS: When posting code, please use the VBA button.

[RockMechMark]

Thanks, Paul.

I will give it a try.

I am not sure that I have found that VBA button. I see one with the ID of "Wrap [VBA] tags". Is that it?

Mark

[macropod]

I am not sure that I have found that VBA button. I see one with the ID of "Wrap [vba] tags". Is that it?
Yes, That's the one.

[RockMechMark]

Paul,

I tried your sample script. Two difficulties:

(1) The statement setting the path and filename of the ASCII file containing plot path and names. It is not finding the file by that name. I believe that I have the right path name and filename. However, is there a character limit on a line? I have 105 characters on that line. I do not think that is the problem, because in the error statement it lists the filename and its extension correctly. I am not sure what is wrong. The statement I have is
StrFile = "C:\Users\" & Environ("Mark") & "\Documents\xxxxxxxxxx\yyyyyyy yyyy yyyy\Plots_to_Import.txt"
where the xxx and yyy characters replace actual characters for privacy.

Also, it did not like the second "Next" statement. I changed it to "End With".

Mark

[RockMechMark]

I think I mischaracterized the problem. The following is the statement that is executed, resulting in the error about not recognizing that filename:
Set wdDoc = Documents.Open(FileName:=StrFile, ReadOnly:=True, _
ConfirmConversions:=False, AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, Encoding:=msoEncodingUTF8)

[macropod]

You should not have changed 'Environ("UserName")' to 'Environ("Mark")'!!
If you wanted to change that, you would use:
StrFile = "C:\Users\Mark\Documents\xxxxxxxxxx\yyyyyyy yyyy yyyy\Plots_to_Import.txt"

And yes, that second 'Next' should have been 'End With'. Apologies for that.

[RockMechMark]

Two things:

(1) I do not get pictures imported, only links. I don't know what I need to do to get those. However, I don't think I want the pictures linked to the original files. How do I break that link in the code? I still do not understand that command with INCLUDEPICTURE.

(2) How can I execute a subroutine for each picture to crop and resize it? Seems that each would have to be selected in a loop and then the subroutine executed. Here is the subroutine:
Sub CropPictue()
'
' CropDemo Macro
'
'
Dim oILS As InlineShape
Set oILS = Selection.InlineShapes(1)
With oILS
.PictureFormat.CropLeft = 15
.PictureFormat.CropTop = 15
.PictureFormat.CropRight = 28
.PictureFormat.CropBottom = 38
End With
With oILS
.LockAspectRatio = True
.Width = 468
End With
lbl_Exit:
Exit Sub
End Sub

Thanks very much.

Mark

[macropod]

If you're only seeing links (looking like {INCLUDEPICTURE "Plotfile" }) that suggests you have Word's field code display toggled 'on'. You can toggle it on/off via Alt-F9.

Give the following a go:
Sub Demo()
Dim wdDoc As Document, StrFile As String, StrPlots As String
Dim i As Long, iShp As InlineShape
StrFile = "C:\Users\" & Environ("UserName") & "\Documents\PlotFile.txt"
Set wdDoc = Documents.Open(FileName:=StrFile, ReadOnly:=True, _
ConfirmConversions:=False, AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, Encoding:=msoEncodingUTF8)
StrPlots = wdDoc.Range.Text
wdDoc.Close
Set wdDoc = Nothing
While InStr(StrPlots, vbCr & vbCr) > 0
StrPlots = Replace(StrPlots, vbCr & vbCr, vbCr)
Wend
With ActiveDocument
For i = 0 To UBound(Split(StrPlots, vbCr)) - 1
.InsertAfter vbCr
Set iShp = .InlineShapes.AddPicture(FileName:=Split(StrPlots, vbCr)(i), _
LinktoFile:=False, Range:=.Range.Characters.Last)
With iShp
.PictureFormat.CropLeft = 15
.PictureFormat.CropTop = 15
.PictureFormat.CropRight = 28
.PictureFormat.CropBottom = 38
.LockAspectRatio = True
.Width = 468
End With
Next
Next
End Sub

[RockMechMark]

I also set margins on the page. I have an error on the line .InsertAfter vbCr that says "Object does not support this property or method". I don't think that my changes affect that, but I could be wrong. Here is my code:
Sub ImportPlots()
'
'
' Declarations
Dim wdDoc As Document, StrFile As String, StrPlots As String
Dim i As Long, iShp As InlineShape
'
' Set document margins
ActiveDocument.PageSetup.TopMargin = 54
ActiveDocument.PageSetup.BottomMargin = 54
ActiveDocument.PageSetup.LeftMargin = 72
ActiveDocument.PageSetup.RightMargin = 72
'
StrFile = "C:\Users\" & Environ("UserName") & "\Documents\xxxxxxxxxx\yyyyyyy yyyy yyyy\Plots_to_Import.txt"
Set wdDoc = Documents.Open(FileName:=StrFile, ReadOnly:=True, _
ConfirmConversions:=False, AddToRecentFiles:=False, _
Format:=wdOpenFormatAuto, Encoding:=msoEncodingUTF8)
StrPlots = wdDoc.Range.Text
wdDoc.Close
Set wdDoc = Nothing
While InStr(StrPlots, vbCr & vbCr) > 0
StrPlots = Replace(StrPlots, vbCr & vbCr, vbCr)
Wend
With ActiveDocument
For i = 0 To UBound(Split(StrPlots, vbCr)) - 1
.InsertAfter vbCr
Set iShp = .InlineShapes.AddPicture(FileName:=Split(StrPlots, vbCr)(i), _
LinktoFile:=False, Range:=.Range.Characters.Last)
With iShp
.PictureFormat.CropLeft = 15
.PictureFormat.CropTop = 15
.PictureFormat.CropRight = 28
.PictureFormat.CropBottom = 38
.LockAspectRatio = True
.Width = 468
End With
Next
End With
End Sub


I changed that second Next to End With.

The links I was referring to earlier looked like the boxes in an e-mail where links are stripped and images need to be downloaded. So the user, if the images are desired, must right-click on one such box and hen give the OK to download the images. I don't recall that procedure worked in this case, but maybe I did it incorrectly.

Getting very close. Thanks again.

Mark

[macropod]

Sorry, that should be .Range.InsertAfter vbCr

[RockMechMark]

I am not sure I understand the While block. Stepping through it shows me that it never executes the statement in that block. So let me say this:

let me represent carriage return like <CR>...
The ASCII file of names of plots looks like
path and name 1<CR>
path and name 2<CR>
...
path and name n<CR>

The <CR> on line n may or may not be there. I can be consistent either way, but may inclination is the have only the number of lines as I have plot names. Does this <CR> matter?

[RockMechMark]

With .Range.InsertAfter vbCr, the error is "Object required." Does this error have to do with not entering the While block?

[RockMechMark]

OK. I think I understand that the While block is to get rid of any extra carriage returns with no plot name between them.

[RockMechMark]

Helps to have a period in front of Range.InsertAfter vbCr

Still some difficulty. Will try to identify.

[RockMechMark]

Paul,

In the ASCII file, I have 47 plot names.

The first import works. I notice that it puts a blank line in front of that picture. I don't think I want the blank line or any characters between the pictures, but that is minor.

The second import does not work. It does not recognize the file. I have checked the path and name and find nothing wrong.

I am grasping at straws now. Is there a limit to the number of characters in a string? The second line of the ASCII file has 72 characters before the carriage return and the third line has more characters than that before the carriage return. The longest line in this file is 79 characters before the carriage return. It is possible that in another file, the number of characters could be more.

[RockMechMark]

What does that underscore character mean?

[macropod]

I don't think I want the blank line or any characters between the pictures, but that is minor.
In that case, delete '.Range.InsertAfter vbCr'.

[macropod]

What does that underscore character mean?
Underscores at the end of a line mean that the property or method continues on the next line

[RockMechMark]

Thanks, Paul.

There is still the problem that the second time an import is exectuted, I get Run-time error 5152: "This is not a valid file name."

I interchanged the order of the first and second names. Again, the first import was successful and the second produced this error. So I know it is not some error in the filename.

I asked about number of characters as a possibility, but I don't think that is the problem. Any ideas?

Mark

[RockMechMark]

Thanks, Paul.

There is still a remaining problem.

The second import results in the error: Run-time error '5151': "This is not a valid file name."

I interchanged the first and second plot names. Again, the second import resulted in the same error. The first import was successful in each case. So the filenames are correct (at least the first and second names).

Any ideas?

Mark

[macropod]

Try changing:
StrPlots = wdDoc.Range.Text
to:
StrPlots = Replace(wdDoc.Range.Text, vbLf, vbCr)

[RockMechMark]

Paul,

That did it. Thank you so very much.

Mark