Solved

OpenOffice macro to make text-file usable as database

Posted on 2011-03-20
3
390 Views
Last Modified: 2012-05-11
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
Comment
Question by:ellegaarddk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 23

Expert Comment

by:redrumkev
ID: 35183078
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
 
LVL 23

Accepted Solution

by:
redrumkev earned 500 total points
ID: 35330133
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
 

Author Closing Comment

by:ellegaarddk
ID: 36899035
Sorry, solved the problem in a cmpletely different way - and totally forgot about the question
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Gain an elementary understanding of Blockchain technology.
We take a look at some of the most common obstacles that IT teams run into as they work relentlessly to keep all the alarms and sirens from going off at once.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question