Solved

OpenOffice macro to make text-file usable as database

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Line Spacing in Word 2 131
Follow Up 12 116
Microsoft Excel 2010, 5 130
Difference between Office 2013 fresh install and update from 2007 5 97
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
As a business owner, there are many things that keep you up at night. Profit margins, employee retention, human resource protocols, whether your product or service will remain competitive. When you own or manage a technology company that operates la…
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.

777 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