[VBA Beginner] Mailmerge csv for macro creating multiple tables

[Mauritz84]

Hi there,

I've just started with vba scripting in word 2010. So bear with me if some of my questions are on a lower level.

I'm trying to create a catalouge and use word 2010 as a template. I'm using a mysql database on a ubuntu with phpmyadmin. There I'm creating a generic database to store information to import into the catalogue.

The approach I'm heading now involes the following steps:

1. export a csv file from the database
2. Use mailmerge to paste these csv onto the word template.
3. Create a macro that automaticly finds these csv text strings and convert them to to word tables (recorded a macro on the "convert text to table" function).

These steps aren't really a problem when it comes to creating just one table (Though I'd have to select the csv text manually to create the first table). But as I said I want to create tables for all the mailmerged csv textstrings.

My problem is my inablility to navigate and select next csv textstring. I've tested
1. Selection.moveDown
2. .MoveUntil
3. Selection.Find

I'll post my attempts here, note that these are on a very beginner level.

I'd appriciate if someone could point me in the right direction/tell me if what I'm doing even is possible.

[vba]

Sub Macro1()
'
' Macro1 Macro
'
'



'// This code creates the first table if it's manually selected
Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=3, _
NumRows:=4, AutoFitBehavior:=wdAutoFitContent
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False

End With



'With Selection.Find
'.ClearFormatting
'.Text = "APPLIKATIONSTABELL"
'.Replacement.ClearFormatting
'.Replacement.Text = "hello"
'.Execute Replace:=wdReplaceAll, Forward:=True, _
'Wrap:=wdFindContinue


'End With

'// Failed attempts to move to the next csv textstring
Selection.MoveDown Unit:=wdLine, Count:=20
Set myRange = ActiveDocument.Words(1)
myRange.MoveUntil Cset:="APPLIKATIONSTABELL", Count:=100

'// Failed attempt to create a second table
'Selection.MoveRight Unit:=wdLine, Count:=16
Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=3, _
NumRows:=4, AutoFitBehavior:=wdAutoFitContent
With Selection.Tables(1)
.Style = "Table Grid"
.ApplyStyleHeadingRows = True
.ApplyStyleLastRow = False
.ApplyStyleFirstColumn = True
.ApplyStyleLastColumn = False


End With

'// Attempt to understand how to navigate
'MsgBox "Line " & Selection.Information(wdFirstCharacterLineNumber)
'Selection.MoveDown Unit:=wdLine, Count:=20, Extend:=wdMove
'MsgBox "Line " & Selection.Information(wdFirstCharacterLineNumber)

'Selection.MoveDown Unit:=wdLine, Count:=1



End Sub

[/vba]

Erik Alm

[macropod]

Hi Erik,

Why not use Word's mailmerge tools and a mailmerge main document with the appropriate structure?

[Mauritz84]

Hi Paul,

Thank you for your awnser!

I considered mailmerge to solve this. But early on I discovered that it wont be able to do what I ask for, and that has lead me to this approach. On simple explanation to why it's not possible is that the the table can vary in size considerably and mailmerge requires a mergefield reference to work. What I'm trying to say is that one page can have 1 row in it's table where another page can have multiple.

Did you have any suggestions on my first questions?

Thanks
Erik

[macropod]

Hi Erik,

I can't see what the table 'size' has to do with it. Point a mailmerge at a table and it'll simply process however many rows there are - it doesn't need to be told beforehand. If you're trying to group data, you can use Word's Catalogue/Directory Mailmerge facility (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://lounge.windowssecrets.com/ind...owtopic=731107
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

For some recent, worked examples, see the attachment to the posts at:
http://www.msofficeforums.com/mail-m...html#post23345
http://www.msofficeforums.com/mail-m...html#post30327

Alternatively, you may want to try a Many-to-One Mail Merge add-in from either:
• Graham Mayor at: http://www.gmayor.com/ManyToOne.htm
• Doug Robbins' Windows Live SkyDrive page at: https://skydrive.live.com/?cid=5aedc...615E886B%21111

[Mauritz84]

Hi again,

Thank u for your useful links. I'm sure they'll be useful, even for what I'm trying to do. But I could'nt let go of my attemp and finally succeded to make the macro do what I'm asking for.

Well it's a start



[VBA]Sub Macro1()
'
' Macro1 Macro
'
'

Dim value1 As String

Set mydocument = ActiveDocument.Content

value1 = "APPLIKATIONSTABELL"

mydocument.Find.Execute findtext:=value1, Forward:=True, Wrap:=wdFindStop


'After you find the heading go down a paragraph and convert the imported 'csvtext to a table for 'every occurence
Do While mydocument.Find.Found
myword = mydocument.Next(Unit:=wdParagraph, Count:=1).Select
Selection.ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=6, _
AutoFitBehavior:=wdAutoFitContent

mydocument.Find.Execute


Loop

End Sub[/VBA]


Cheers
Erik Alm