Use a dialog box to fill a document

Creating a macro - Writing a Script - Using the API

Use a dialog box to fill a document

Postby Memohzo » Mon Sep 18, 2017 11:48 pm

Hello everyone!

Need a little help here. I have a VBA macro that works like a charm to fill a document using a dialog box, sending the sext to the specified bookmarks inside the doument. I'm using this to simplify the filling process of a standard letter. I'll be putting it right here:

Code: Select all   Expand viewCollapse view

Private Sub OkButton_Click()
'Client Name
    Dim NombreCliente As Range
    Set NombreCliente = ActiveDocument.Bookmarks("NombreCliente").Range
    NombreCliente.Text = Me.NombreText.Value
'Ticket Number
    Dim intFolio As Range
    Set intFolio = ActiveDocument.Bookmarks("IntFolio").Range
    Dim tbFolio As Range
    Set tbFolio = ActiveDocument.Bookmarks("TablaFolio").Range
    intFolio.Text = Me.Folio.Value
    tbFolio.Text = Me.Folio.Value
'Account
    Dim tbCuenta As Range
    Set tbCuenta = ActiveDocument.Bookmarks("TablaCuenta").Range
    Dim sCuenta As Range
    Set sCuenta = ActiveDocument.Bookmarks("SigueCuenta").Range
    tbCuenta.Text = Me.Cuenta.Value
    sCuenta.Text = Me.Cuenta.Value
   
'Initial Date
    Dim tbFechaRec As Range
    Set tbFechaRec = ActiveDocument.Bookmarks("FechaReclamo").Range
    tbFechaRec.Text = Me.DiaRec.Value + " - " + Me.MesRec.Value + " - " + Me.AnRec.Value

'Final Date
    Dim tbFechaAbo As Range
    Set tbFechaAbo = ActiveDocument.Bookmarks("FechaAbono").Range
    tbFechaAbo.Text = Me.DiaAb.Value + " - " + Me.MesAb.Value + " - " + Me.AnAb.Value

' Table Filling Section

   Dim arrTab1() As String
    Dim oCtr As Control
    Dim i As Long
    i = 0
    Dim oTbl As Word.Table
    For Each oCtr In Me.Controls
        Select Case oCtr.Name
        Case "Mo1", "Mo2", "Mo3", "Mo4", "Mo5"
            If Me.Controls(oCtr.Name).Text <> "" Then
                i = i + 1
            End If
        End Select
    Next oCtr
    ReDim arrTab1(i - 1, 3)
   
    i = 0
    If Me.Mo1 <> "" Then
        arrTab1(i, 2) = "Cargo"
        arrTab1(i, 1) = "$ " + Me.Mo1
        arrTab1(i, 0) = "A Favor"
        i = i + 1
    End If
   
    If Me.Mo2 <> "" Then
        arrTab1(i, 2) = "Intereses"
        arrTab1(i, 1) = "$ " + Me.Mo2
        arrTab1(i, 0) = "A Favor"
        i = i + 1
    End If
   
    If Me.Mo3 <> "" Then
        arrTab1(i, 2) = "Comisiones"
        arrTab1(i, 1) = "$ " + Me.Mo3
        arrTab1(i, 0) = "A Favor"
        i = i + 1
    End If
   
    If Me.Mo4 <> "" Then
        arrTab1(i, 2) = "Puntos"
        arrTab1(i, 1) = Me.Mo4 + " Pts"
        arrTab1(i, 0) = "A Favor"
        i = i + 1
    End If
   
     If Me.Mo5 <> "" Then
        arrTab1(i, 2) = Me.Especif
        arrTab1(i, 1) = "$ " + Me.Mo5
        arrTab1(i, 0) = "A Favor"
        i = i + 1
    End If
   
    Set oTbl = ActiveDocument.Tables.Add(ActiveDocument.Bookmarks("TableLoc").Range, i + 1, 3)
    oTbl.Cell(1, 1).Range.Text = "Tipo de Monto"
    oTbl.Cell(1, 2).Range.Text = "Monto"
    oTbl.Cell(1, 3).Range.Text = "Resolución"
    For i = 1 To UBound(arrTab1) + 1
        oTbl.Cell(i + 1, 3).Range.Text = arrTab1(i - 1, 0)
        oTbl.Cell(i + 1, 2).Range.Text = arrTab1(i - 1, 1)
        oTbl.Cell(i + 1, 1).Range.Text = arrTab1(i - 1, 2)
    Next i

For p = 1 To i
    With oTbl.Cell(p, 2)
    .Width = InchesToPoints(1.5)
    End With
Next p

For p = 1 To i
    With oTbl.Cell(p, 1)
    .Width = InchesToPoints(1)
    End With
Next p

For p = 1 To i
    With oTbl.Cell(p, 3)
    .Width = InchesToPoints(2)
    End With
Next p

For Each oCell In oTbl.Range.Cells
  oCell.Range.Font.Name = "Stag Sans Book"
  oCell.Shading.BackgroundPatternColor = RGB(181, 229, 249)
  oCell.Range.Font.ColorIndex = wdBlack
  oCell.Range.Font.Size = 12
  oCell.Width = InchesToPoints(2)
  oCell.VerticalAlignment = wdCellAlignVerticalCenter
Next oCell

For p = 1 To 3
    With oTbl.Cell(1, p)
    .Height = InchesToPoints(0.3)
    .Shading.BackgroundPatternColor = RGB(0, 158, 229)
    .Range.Font.Name = "Stag Sans Book"
    .Range.Font.ColorIndex = wdWhite
    .Range.Font.Size = 12
    End With
Next p

For Each oCell In oTbl.Range.Cells
  oCell.VerticalAlignment = wdCellAlignVerticalCenter
Next oCell


'Close
    Me.Repaint
    AcInfo.Hide
End Sub


I need to do the same thing but in OpenOffice. Any ideas? I've tried looking into the forums but I haven't found any similar examples I can adapt or use as a reference.
OpenOffice 4.1.3 on Windows 7
Memohzo
 
Posts: 1
Joined: Mon Sep 18, 2017 11:36 pm

Re: Use a dialog box to fill a document

Postby Zizi64 » Tue Sep 19, 2017 7:44 am

If you can not rawrite the macros based on the API functions of the AOO/Lo, then you must try them in the LibreOffice. The LO has a littlebit higher compatibility with the foreign fileformats and the VBA macros.

Use the

Code: Select all   Expand viewCollapse view
Option VBASupport 1
Option Compatible


parameters.

Otherwise you must rewrite all of your macros.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7988
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests