Solved: VBA Automation image insert second page

[Movian]

Hi,
i switched from early to late binding a few weeks ago and have recently discovered a problem.

Previously my code red a text file that contained information on adding an image to a word document. This file sometimes provides an anchor that indicates the image must be set to page 2, now previously i simply passed this anchor "WordApp.ActiveDocument.Range.GoTo(wdGoToPage, wdGoToNext, 2)"

as the anchor property when i inserted the image through vba.

However this is no longer working and i have had no luck in tryign to figure out why let alone fixing it. here is my sub that inserts the image

Public Sub AddDiagram(left As Integer, top As Integer, width As Integer, height As Integer, file As String, anchor As String)
'Imports Diagram loops through all shape objects to find the diagram, then selects it
'and puts it behind the text so as to keep formating correct.
If fso.FileExists(dfol + "\" + Forms("frmMain").ReportDate + "_" + file) Then
If anchor = "Null" Then
WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height
Else
WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height, anchor
End If
For J = 1 To doc.Shapes.count
If Mid(doc.Shapes(J).Name, 1, 7) = "Picture" Then
image = doc.Shapes(J).Name
doc.Shapes(J).select
End If
Next J
WordApp.selection.ShapeRange.WrapFormat.Type = 3
WordApp.selection.ShapeRange.ZOrder 5
doc.save
WordApp.ActiveDocument.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
If MsgBox("Unable to find diagram. Would you like to create the report anyway without the diagram?", vbYesNo, "No Diagram Found") = vbYes Then
doc.save
WordApp.ActiveDocument.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
WordApp.ActiveDocument.save
WordApp.ActiveDocument.Close
Kill fol + "\" + Forms("frmMain").ReportDate + "_" + killrepname + ".doc"
End If
End If
End Sub


any assistance with this would be apriciated

[CreganTur]

Welcome to the forum! Always good to see new members.

My first question is why did you switch to late binding? Are you seeking version-independence for Word, or is there some other reason?

Secondly, I understand that this Sub is part of a larger project, but at first glance it seems like you are not using Option Explicit, which is a very bad idea. Either that or you declared public variables outside of the Sub, which is fine and explains why I can't see them.

When you say it isn't working, what exactly do you mean? Is the image not moving to the second page, or are you getting an error message?

[Movian]

i was trying to do several things by switching.

Firstly as you assumed i was trying to get some version independence (Some of our clients have office 2003 some 2007 (and some even 2000)) which was causing issues. Secondly i was trying to remove the reference as we were having many issues with the references getting fuubared. So the removal of the refrence was a breath of fresh air allround EXCEPT for the intellisence (Wish they could have a plugin pack or somthing that lets you use latebinding but still use intellisence).

i am using Option Explicit and yes i do have some public vars declared in the module


Option Compare Database
Option Explicit

'Richard Burgess - 08/22/2008
'Module that can create a word document
'Can also be used to imort diagrams and specified document locations by passing the location as function options

Public WordApp As Object
Public doc As Object
Public newdoc As String
Public fol As String
Public dfol As String
Public Sfol As String
Public image As String
Public oBookmark
Public J As Integer
Public fso
Public length As Integer
Public bookname As String
Public bookrange As String
Public reports() As String
Public max As Integer
Public loc As String
Public temploc As String
Public killrepname As String
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Integer) As Long
Const wdGoToLine = 3
Const wdGoToAbsolute = 1
Const wdGoToPage = 1

note i tried to resolve the problem by declaring some the of the global constants for the anchor.

after playing around with the code for a while i had several diffrent resulsts

either the code will crash out with no error and without importing the diagram and leaving a winword process running (which is a pain cos i have to close it in taskmanager).
ALternativly it will simply import the diagram to the first page. Or it may give me the error

5852 - Requested Object is not available.

Note each of those scenarios is with slightly altered code.
The base problem with the code shown is the 5852 error shown and the

WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height, anchor" line shown as the problem

[CreganTur]

I placed a post in the Word Help forum to see if anyone there may have an idea. The extent of my Word OLE Automation involves populating form letters by filling bookmark ranges :razz:

I'm also not sure what to do since you get varying errors.

I know that this worked for you via Early Binding, so having to create a new solution is an idea you're probably loathe to entertain, but this may be a workaround... or it may be a stupid idea- I can't tell which.

In your first post you mention that the document itself contains the anchor that will determine if the picture goes on page 2. Is there a way for you to evalaute that anchor and then, if page 2 condition is true, explicitly insert the picture on page two, but do it independently from the anchor? Perhaps insert the picture at a bookmark range or something similar?

[Movian]

that would be an option, now the question being how would i insert the image at the bookmark? i know how to replace the inside of a bookmark with text :P
that is what the other portion of this module does.
But images are a little more complex (as i have recently been finding out).
So if you could give me some advice on that i would appreciate that

*could i do somthing along the lines of
obookmark("BookmarkName").range.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height

would that work ?

[CreganTur]

If I'm correct you could do it like this:



WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file _
, False, True, Range:=ActiveDocument.Bookmarks("BookmarkName").Range




Where 'BookmarkName' would be the actual name of the bookmark where you want to insert the picture.
You might have to use this since it is an external object:



WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file _
, False, True, Range:=WordApp.ActiveDocument.Bookmarks("BookmarkName").Range

[Movian]

AHH bugger,
now i remembered why im doing it the way i am LOL.

The first thing that the system does is it runs through every bookmark in the document and replaces it with information that has been retrieved from the database. If a Bookmark dosn't exist it removes it so that the finished document doesn't have any left over nasty looking bookmarks. However This means that when it comes to insert the image there is no bookmark any more and it can't insert it. Meaning i need to use an anchor that can simply force the image to be insert at the top, left, height, width co ords specified just on the second page.

lol

[CreganTur]

It's one of those :doh: moments.

You could workaround this if you know the name of the bookmark where you want to insert the picture ahead of time- use an If statement to check the names of the bookmarks before they are deleted. If it encounters the name you want, then it skips to the next bookmark without deleting it.

[Movian]

The Problem Being that some documents have Multiple images that need to be placed on the second page. the system wont know how many until it reads the file.

[CreganTur]

Well... here's another idea for you:

Since Early Binding worked perfectly for you, except for the little Word versions problem, you could use Conditional Compiler Constants (#Const) as switches. Check to see which version of Word the User has, and then use the correct #Const Directive to run your code for the correct version of Word.

Doing this does mean that you would have to include the specific coding for 2000, 2003, & 2007... may be more trouble than its worth... plus someone else may come along with the exact answer you need.

[Movian]

ok i have a solution working.

although im sure i had tried it before i guess it just didn't like my globabl consts.

If fso.FileExists(dfol + "\" + Forms("frmMain").ReportDate + "_" + file) Then
If anchor = "Null" Then
WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height
Else
WordApp.ActiveDocument.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height, WordApp.ActiveDocument.Range.Goto(1, 2, 2)
End If

after a little further investigation i discovered that its fine if i hardcode the information in, however as soon as i try and pass it as a String or Variant type variable then the system kicks up a fuss, What i guess i will do is simply have a number in the text file read in that will represent that page number, then have the line hard coded with just the page number as an integer


*EDIT

This is what my Sub Turned out like after i had finished making my small alterations, just to bring it back in line with my own personal coding Syntax
Public Sub AddDiagram(left As Integer, top As Integer, width As Integer, height As Integer, file As String, PageNum As Integer)
'Richard Burgess 2008
'Imports a diagram then loops through all images in document untill it finds the image just imported
'Then places the image Behind the text
If fso.FileExists(dfol + "\" + Forms("frmMain").ReportDate + "_" + file) Then
doc.Shapes.AddPicture dfol + "\" + Forms("frmMain").ReportDate + "_" + file, False, True, left, top, width, height, WordApp.ActiveDocument.Range.Goto(1, 2, PageNum)
For J = 1 To doc.Shapes.count
If Mid(doc.Shapes(J).Name, 1, 7) = "Picture" Then
doc.Shapes(J).select
End If
Next J
WordApp.Selection.ShapeRange.WrapFormat.Type = 3
WordApp.Selection.ShapeRange.ZOrder 5
doc.save
doc.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
If MsgBox("Unable to find diagram. Would you like to create the report anyway without the diagram?", vbYesNo, "No Diagram Found") = vbYes Then
doc.save
doc.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
doc.save
doc.Close
Kill fol + "\" + Forms("frmMain").ReportDate + "_" + killrepname + ".doc"
End If
End If
End Sub
I made minor alterations based on this new syntax requirement (why i can't pass an anchor as a string value i have no idea).
It will now be able to place an image on any page in a document as long as its specified in the text file that is read in, and i was able to remove and If Statement in the process to help optimize the code somewhat.

Any other optimizations are welcome :)

[Tommy]

Any other optimizations are welcome :devil2:

& are faster than + in string conco.

Why do you use
WordApp.Visible = True
WordApp.Activate
?


Public Sub AddDiagram(left As Integer, top As Integer, width As Integer, _
height As Integer, file As String, PageNum As Integer)
'Richard Burgess 2008
'Imports a diagram then loops through all images in document untill it finds the image just imported
'Then places the image Behind the text

'Tommy's opinion
Dim FleStr As String, RetVal As Variant
FleStr = dfol & "\" & Forms("frmMain").ReportDate & "_" & file
'
If fso.FileExists(FleStr) Then '<- applied opinion
doc.Shapes.AddPicture FleStr, False, True, left, top, width, height, _
WordApp.ActiveDocument.Range.GoTo(1, 2, PageNum) '<- applied opinion
For J = 1 To doc.Shapes.Count
If Mid(doc.Shapes(J).Name, 1, 7) = "Picture" Then
doc.Shapes(J).Select
End If
Next J
WordApp.Selection.ShapeRange.WrapFormat.Type = 3
WordApp.Selection.ShapeRange.ZOrder 5
doc.Save
doc.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
'more of Tommy's opinion
Dim Message As String, MsgTitle As String
Message = "Unable to find diagram. Would you like to create the report anyway without the diagram?"
MsgTitle = "No Diagram Found"
'
RetVal = MsgBox(Message, vbYesNo, MsgTitle) '<- applied opinion
If RetVal = vbYes Then
doc.Save
doc.ActiveWindow.View.Type = 3
WordApp.Visible = True
WordApp.Activate
Else
doc.Save
doc.Close
Kill fol & "\" & Forms("frmMain").ReportDate & "_" & killrepname & ".doc"
End If
End If
End Sub

[Movian]

When i first create the Word object file it is invisible which saves screen updates as well as i don't wish the end user to see the system actively performing its operations. So once everything is finished i make it "visible" however while it may be visible it may not be the application at the front (as Access runtime is open as well) so the activate command is suposed to force the program to apear as the front program running meaning they view the finished report as soon as the system has finished creating it.