Solved

OpenOffice macro to make text-file usable as database

Posted on 2011-03-20
3
379 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formatting BSEeq 12 87
Perplexing Blue Screen Issue for One User 15 60
Office 365 desktop software licenses? 3 67
Access 2016 Forms 4 63
Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now