Xojo and VBA

  1. What are the principles of converting MS Office VBA code into Xojo VBA code; any differences?
  2. The principles of converting vbscript into MS Office VBA/Xojo VBA?
  3. I have a working vbscript code (see below) that replaces a piece of text in a doc file with an image and a failed attempt to do the same using Xojo VBA.

vbscript:

Dim oWord
Err = 0
On Error Resume Next
'Set oWord = GetObject("Word.Application")
Set oWord = CreateObject("Word.Application")
oWord.Visible = TRUE
oWord.displayalerts=false
oWord.Application.ScreenUpdating = False

oWord.Documents.add("C:\\tpl.dotx")

Set objSelection = oWord.Selection
 Set objShape = objDoc.Shapes

'oWord.ActiveWindow.ActivePane.View.ShowAll = True

oWord.Selection.Find.Text = "<<image>>"
oWord.Selection.Find.Forward = True
oWord.Selection.Find.Wrap = wdFindContinue
oWord.Selection.Find.Format = False
oWord.Selection.Find.MatchCase = False
oWord.Selection.Find.MatchWholeWord = False
oWord.Selection.Find.MatchWildcards = False
oWord.Selection.Find.MatchSoundsLike = False
oWord.Selection.Find.MatchAllWordForms = False

while oWord.Selection.Find.Execute = True

'objSelection.TypeText "TEXT"

'oWord.Selection.objShape.AddPicture("C:\\this.png")

Set pic = oWord.Selection.InlineShapes.AddPicture("C:\\this.png") ' FileName:="C:\\IMAGE\\myimage.TIF", LinkToFile:=False, SaveWithDocument:=True
pic.Height = 100
pic.Width  = 200

Wend

oWord.Application.ScreenUpdating = True
'oWord.ActiveWindow.ActivePane.View.ShowAll = False
 'oWord.Documents(1).Protect wdAllowOnlyFormFields, True
 'oWord.ActiveDocument.Save

VBA

  Dim word as WordApplication
  Dim doc as WordDocument
  
  word = New WordApplication
  word.visible = True
  doc = Word.Documents.Add("c:\\tpl.dotx", true)
  
  Dim find as WordFind
  find = word.selection.find
  Find.Text="<<image>>" //Find field
  Find.Replacement.Text=""
  
  Find.Forward = True
  Find.Wrap = 1
  Find.Format = False
  Find.MatchCase = False
  Find.MatchWholeWord = False
  Find.MatchWildcards = False
  Find.MatchSoundsLike = False
  Find.MatchAllWordForms = False
  
  Dim oleparam as New OLEParameter
  
  oleparam.value = office.wdReplaceAll
  oleparam.Position = 11
  
  find.execute oleparam
  'word.selection.GoTo_ (,,,)
  word.selection.Range.InlineShapes.AddPicture ("c:\\this.png")

[quote=225473:@Tim Kay]1. What are the principles of converting MS Office VBA code into Xojo VBA code; any differences?
[/quote]

Hello Tim,

There are very few differences when converting VBA to Xojo

vbscript conversion will take more familiarity with Xojo to convert.

The first few lines of VBA directly convert to Xojo, and here is a portion of the working program code in Xojo which works on my computer:

[code] Dim word as new WordApplication
Dim doc as WordDocument
word.visible = True
word.Documents.add(“c:\test\tpl.dotx”,true)

Dim find as WordFind
find = word.selection.find
find.text="<>"
… [/code]

There are lots of examples and people in the Xojo forums who are willing to help, and there is documentation on the online docs: Word Application , Office Automation and third party books to help you program Word with Xojo.

Happy to help.

if you use the WindowsScriptMBS class in MBS Plugins, you can run VBScript code directly.

see
http://www.monkeybreadsoftware.net/class-windowsscriptmbs.shtml

Christian,
I will use your plugin as an option., I already have a license.

Yet ih the case described all I’m missing is how to exactly place the image.

Here is how to add an image:

word.Selection.InlineShapes.AddPicture("C:\\Test\\Graphic200.png")

Happy to help.

Placement of the image depends on the type of area.

Are you placing the image in the document header? Table? Paragraph? … and more.

Hi, Eugene. I found the solution just didn’t have the time to post it.
So many have visited the topic and most likely, none had the answer. I will be glad to know this solution helps others.

The tricky part is in “find.execute”. The documentation had a text replacement example with “find.execute oleparam” performing a string-with-string substitution. It took time to figure where the “while” clause should go. So I chanced to replace the “oleparam” parameter for FIND.EXECUTE original line with “while find.execute = true” … “wend”

Here’s a code that finds the marker (<>) in the document and replaces it with an image:

Sub replace_vbaword(template As String, type() As String, marker() As String, value() As String, picHeight as integer=100, picWidth as integer = 200, savePath As String = "")
  'Use the method like so:
  'dim type(), marker(), value() As String
  'type = array("file", "text")
  'marker = array("<<image>>", "<abc>")
  'value = array("c:\\this.png", "TADA!")
  '
  'replace_vbaword ("c:\\tpl.dotx", type, marker, value, 0,0 , "c:\\doneVBA.docx" )
  
  if picHeight = 0 Then picHeight=100 ' just in case, you know
  if picWidth = 0 Then picWidth = 200 ' same here
  
  Dim word as WordApplication
  Dim doc as WordDocument
  
  word = New WordApplication
  word.visible = False
  doc = Word.Documents.Add(template, false) ' add (template, false)- NB! the second parameter must be false to save a valid document file 
  
  Dim find as WordFind
  Dim oleparam as New OLEParameter
  
  oleparam.value = office.wdReplaceAll
  oleparam.Position = 11
  
  dim j, i As Integer = 0
  j= marker.Ubound
  for i=0 to j ' each member as String in type
    
    
    find = word.selection.find
    Find.Text= marker(i)
    Find.Forward = True
    Find.Wrap = 1
    Find.Format = False
    Find.MatchCase = False
    Find.MatchWholeWord = True
    Find.MatchWildcards = False
    Find.MatchSoundsLike = False
    Find.MatchAllWordForms = False
    Find.Replacement.Text=value(i)
    'MsgBox marker(i)
    if type(i)<>"file" Then ' replace marker with string value
      
      'MsgBox value(i)
      find.execute oleparam '
      
    elseif type(i) = "file" Then ' replace marker with image
      Dim wrdPic As WordInlineShape
      while find.execute = true
        wrdPic = word.selection.InlineShapes.AddPicture (value(i))
        wrdPic.Height = picHeight
        wrdPic.Width = picWidth
      wend
      
    end if ' type
    'i=i+1
  next ' member '
  
  word.visible =True
  if savePath ="" Then ' let the user interact with the document
    
  else ' archive the file silently
    doc.SaveAs(savePath) ' check this: Word.Documents.Add(template, false) 
    doc.Close
     Word.Quit
    
  end if
  
Exception err as OLEException
  MsgBox err.message
  
  
End Sub

Hi,
I’ve tried the Tim solution.
It works well, replace both image and text if the marker are in body area.
But if I put the <> marker inside “heading” or “footer” side it is not replaced.

There is a way to put the image in document heading ?

Thanks in advances

Luciano,
check the documentation whether heading and footer image holders are implemented differently.
C’mon, do some web search :slight_smile:
“vba word change image in header”
Check this out:
https://www.thespreadsheetguru.com/the-code-vault/2014/6/2/insert-picture-into-word-header-with-vba
https://superuser.com/questions/1053398/insert-picture-in-header-with-vba-from-picture-located-in-workbook

Thanks Tim.

Hi,
I’ve added a code for add a picture in Header section.

'Use the method like so:

'dim type(), marker(), value() As String
'type = array(“himage”, “bimage”, “text”)
'marker = array("<>", “<>”, “”)
'value = array(“c:\logo1.png”, “c:\logo2.png”, “some text”)
'FillWordTemplate (“c:\template.dotx”, type, marker, value, 0, 0, 0, 0,“c:\doneVBA.docx” )

Thanks to all.

Sub FillWordTemplate (template As String, type() As String, marker() As String, value() As String, HpicHeight as integer=100, HpicWidth as integer = 200, picHeight as integer=100, picWidth as integer = 200,savePath As String = “”)

if picHeight = 0 Then picHeight=100 ’ just in case, you know
if picWidth = 0 Then picWidth = 200 ’ same here

if HpicHeight = 0 Then picHeight=100
if HpicWidth = 0 Then picWidth = 200

Dim word as WordApplication
Dim doc as WordDocument

word = New WordApplication
word.visible = False
doc = Word.Documents.Add(template, false) ’ add (template, false)- NB! the second parameter must be false to save a valid document file

Dim find as WordFind
Dim oleparam as New OLEParameter

oleparam.value = office.wdReplaceAll
oleparam.Position = 11

dim j, i As Integer = 0
j= marker.Ubound
for i=0 to j ’ each member as String in type

if type(i) = "himage" then ' add pic to header
  Dim wrdHPic As WordInlineShape
  wrdHPic = doc.Sections.Item(1).Headers(Office.wdHeaderFooterPrimary).Range.InlineShapes.AddPicture (value(i))
  wrdHPic.Height = HpicHeight
  wrdHPic.Width = HpicWidth
else
  
  find = word.selection.find
  Find.Text= marker(i)
  Find.Forward = True
  Find.Wrap = 1
  Find.Format = False
  Find.MatchCase = False
  Find.MatchWholeWord = True
  Find.MatchWildcards = False
  Find.MatchSoundsLike = False
  Find.MatchAllWordForms = False
  Find.Replacement.Text=value(i)
  'MsgBox marker(i)
  if type(i) = "text" Then ' replace marker with string value
    
    'MsgBox value(i)
    find.execute oleparam '
    
  elseif type(i) = "bimage" Then ' replace marker with image in body
    
    Dim wrdPic As WordInlineShape
    while find.execute = true
      wrdPic = word.selection.InlineShapes.AddPicture (value(i))
      wrdPic.Height = picHeight
      wrdPic.Width = picWidth
    wend
    
  end if ' type
  
end if

'i=i+1

next ’ member ’

word.visible =True
if savePath ="" Then ’ let the user interact with the document

else ’ archive the file silently
'doc.SaveAs(TransmittalFolder.NativePath + str(Today.year,“0000”) + “-” + str(Today.Month,“00”) + “-” + str(Today.Day,“00”) + "_Trans - " + sentto + “.docx”)
doc.SaveAs(savePath) ’ check this: Word.Documents.Add(template, false)
doc.Close
Word.Quit

end if

Exception err as OLEException
MsgBox err.message

End Sub

Please use the code icon above the editor when you post code :

ok thanks