Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

OpenOffice macro to make text-file usable as database

I don't know much about OpenOffice, but now I need to know a lot!

I need to use an output form one program (text file) as a database for adresses in a standard letter merge in Writer.

The output is a standard text-file containing a lot of addresses. Each address is 6 lines.

I hve to transform this six line address into something like a CSV file: 6 fields per address, semicolon as delimiter, preferrably contained in quotes - all neatly presented in one line per address.

And the solution has to be fool proof. The user, that will be using this on a monthly basis, knows just enough to folow a guide on how to do the merging from a template. So this has to be done almost automatic.

How do I solve this??
0
ellegaarddk
Asked:
ellegaarddk
  • 2
1 Solution
 
redrumkevCommented:
ellegaarddk,

How are you doing?

Could you please post some test data in the following formats.

1. The original data in a text file as it comes from your database (change names/address numbers, etc before posting if the information is private) but please try to keep the structure the same.

2. Can you take the same data and post a file of "how you want it to be", as a before and after.

This will give me something to go on.

Thank you,
Kevin
0
 
redrumkevCommented:
ellegaarddk,

Try this:

Function STRJOIN(range, Optional delimiter As String, Optional before As String, Optional after As String)
    Dim row, col As Integer
    Dim result, cell As String

    result = ""

    If IsMissing(delimiter) Then
        delimiter = ";"
    End If
    If IsMissing(before) Then
        before = ""
    End If
    If IsMissing(after) Then
        after = ""
    End If

    If NOT IsMissing(range) Then
        If NOT IsArray(range) Then
            result = before & range & after
        Else
            For row = LBound(range, 1) To UBound(range, 1)
                For col = LBound(range, 2) To UBound(range, 2)
                    cell = range(row, col)
                    If cell <> 0 AND Len(Trim(cell)) <> 0 Then
                        If result <> "" Then
                            result = result & delimiter
                        End If
                        result = result & before & range(row, col) & after
                    End If
                Next
            Next
        End If
    End If

    STRJOIN = result
End Function

Open in new window


If your data is cell A1:A300 for example and you do no have a header, then you can have this count rows 1-6, join those, then start counting again 7-12 (second address), 13-18 (third address).

If you have a header, then we start at A2, so rows 2-7 (1st address), 8-13 (second), 14-19 (third and so on..).

If you have a header and a space between each, then we are going to want to count like this:

Start at 2 (header is 1) then run 2-7, row 8 is a space, so 9-14, with 15 for a space. This could be an array (1-6) that adds 7 to the base number of 2 after each loop until the end of your range.

Kevin
0
 
ellegaarddkAuthor Commented:
Sorry, solved the problem in a cmpletely different way - and totally forgot about the question
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now