Help with a mailmerge list

[Tinku]

Hi I am a newbie to VBA and my 1st post on this forum, I am trying to create a MailMerge excel file to be used in Word.
Right now the data is like this

Company Name Address DUNS #

WORKSHIFT SYSTEMS CORP 373 Poulin Avenue 251290813
OTTAWA
K2B 5V2

XBASE TECHNOLOGIES CORP 20 Eglinton Avenue West 248627911
Suite 1300
Box 2006
Toronto

I want this data to go into columns like this. 1 Row for each record so that I can export it into excel and use it for mailmerge.

Company Name Address Address2 Address3 Zip City DUNS #

WORKSHIFT SYSTEMS CORP 373 Poulin Avenue OTTAWA K2B 5V2 251290813
XBASE TECHNOLOGIES CORP 20 Eglinton Avenue West Suite 1300 Box 2006 Toronto

Is there an easy way to achieve this ? I dont want to do it manually as it has some 16000 rows.

Regards
Tinku

[sandam]

Quick question, where is your data coming from? Is it another XL file?

[Tinku]

Oops Sorry about that.. I have exported the data into an excel file..

Regards

Tinku

[sandam]

okay. now heres the fun bit. are those three lines each in a cell(s) or is all that information in one cell? I have some code that can help you but I need to know how the data is arranged in the excel sheet.

eg
---------------------------------------------------------
|WORKSHIFT SYSTEMS CORP 373 Poulin Avenue 251290813|
|OTTAWA |
|K2B 5V2 |
----------------------------------------------------------
or
-----------------------------------------------------------
|WORKSHIFT SYSTEMS CORP 373 Poulin Avenue 251290813|
-----------------------------------------------------------
|OTTAWA |
-----------------------------------------------------------
|K2B 5V2 |
-----------------------------------------------------------
or
-----------------------------------------------------------
|WORKSHIFT SYSTEMS CORP | 373 Poulin Avenue | 251290813|
-----------------------------------------------------------
|OTTAWA | | |
-----------------------------------------------------------
|K2B 5V2 | | |
-----------------------------------------------------------

once I know these things, I should be able to sort ya out in no time.

Andrew;?

one last thing - are there open lines between data or does each one follow the other straightaway?

eg
---------------------------------------------------------
WORKSHIFT SYSTEMS CORP 373 Poulin Avenue 251290813
OTTAWA
K2B 5V2
----------------------------------------------------------

----------------------------------------------------------
XBASE TECHNOLOGIES CORP 20 Eglinton Avenue West 248627911
Suite 1300
Box 2006
Toronto
-----------------------------------------------------------
OR
---------------------------------------------------------
WORKSHIFT SYSTEMS CORP 373 Poulin Avenue 251290813
OTTAWA
K2B 5V2
----------------------------------------------------------
XBASE TECHNOLOGIES CORP 20 Eglinton Avenue West 248627911
Suite 1300
Box 2006
Toronto
-----------------------------------------------------------

[Tinku]

Thank you Sandam for your patience, the data is as follows

-----------------------------------------------------------
|WORKSHIFT SYSTEMS CORP | 373 Poulin Avenue | 251290813|
-----------------------------------------------------------
| | OTTAWA| |
-----------------------------------------------------------
| | K2B 5V2| |
-----------------------------------------------------------

The data is on 3 diff rows. and 3 columns as follows
|Company Name | Address | Duns #
|WORKSHIFT SYSTEMS CORP |373 Poulin Avenue | 251290813|
| | OTTAWA|
| | K2B 5V2|

Regards
Tinku

[Zack Barresse]

If you'd prefer, Tinku, you can zip and upload a sample file. Just use the Manage Attachments button in the Reply screen (cannot use Quick Reply).

[Tinku]

Thanks

I tried clicking on the Manage Attachments button..doesnt do anything for me.. the status bar on the bottom says popup blocked.. I disable my Zonelabs firewall and Norton AV, but still get the same error..

Any ideas ?

Regards
Tinku

[Zack Barresse]

email it to me at ..

firefytr at vbaexpress dot com

Attach as any type, I can zip.

[Tinku]

I u/l it here

http://rapidshare.de/files/915516/sample.xls.html

Please see if it works

Regards
Tinku

[Anne Troy]

Tinku: Next time, hold your control key while you click the "manage attachments" button. Most will bypass the "no popup" if you do that.

[Tinku]

Wow.. thanks Dreamboat.. you guys amaze me.. was that "CTRL" keypress specific to your code or it some Windows thingy..
U/l the file successfully..thks to Dreamboat..

Regards
Tinku

[Anne Troy]

Well...I use the Google toolbar and I know it works there. I've also seen others tell others to use the Ctrl key, so I assume it's not necessarily a Windows thing, but something that all popup blockers are adhering to as a "standard", you know?

Good luck with your project...
Also, you and Sandam may want to see if this KB entry suits your needs before reinventing the wheel: http://www.vbaexpress.com/kb/getarticle.php?kb_id=172

Congrats, Sandam, on your new position as Approver!!

[sandam]

Thanks Dreamboat. Still in a bit of shock about that. Now Tinku, about using DRJ's excellent KB entry so kindly pointed out by Dreamboat. I fiddeled with the code to work for you. So here it is. I must point out a few things though. It works very well as long as the data follows the structure below. I ran it with a couple of misplaced items and it messed up a bit, however not to discourage you, I'm quite sure your exported data fits the format fine. I suggest make a back up copy and then run the code below.

-------------------------------------------------
|Customer Number|Address Line 1|Affil Number
-------------------------------------------------
||Address Line 2||
-------------------------------------------------
||Address Line 3||
-------------------------------------------------
||Post Code||
-------------------------------------------------

[vba]
Option Explicit
Sub FixFormat()
Dim LastRow As Long
Dim x As Long
'This line finds the last row of the column with all the address information in it
LastRow = Range("B65536").End(xlUp).Row
For x = LastRow To 2 Step -1
'we check the A row becase that is the one with the customer number in it
If Range("A" & x).Value = "" Then
'if it comes across a blank cell in the a column
'then it moves the B column data to the 'cell
'above it and deletes the whole empty row.
'This can be made to work on multiple columns if you need it to
Range("B" & x - 1).Value = Range("B" & x - 1).Value & " " & Range("B" & _
).Value
Range("B" & x).EntireRow.Delete
End If
Next x
End Sub
[/vba]

lastly - I'm gonna play with this a little more so that each time it moves up a row, it inserts a column an put the data there instead - so that your information is in seperate columns for a mail merge

[Anne Troy]

LOL, sandam. Just watch the width of the page by making long comments shorter, like I did when I edited your VBA code above.

[Tinku]

Thanks Sandman.. the code works.. but it concatenates everything into one cell..whereas I want to use this list for mailmerge.. as you have already pointed it out.. I will wait for your code and thank you and dreamboat for your efforts.

Regards
Tinku

[sandam]

I'll continue to work on this , its little more perplexing than i first thought.

[Anne Troy]

Tinku: Can you provide a sample of the data structure AFTER the macro?

Oh! And do you need to do this on a regular basis? Or just this once?

[Tinku]

Hi Dreamboat..

I ran the macro but didnot save the results as they were not what I was looking for. I intend to use it more often than I initially thought as I keep getting such data often in .csv format. I already have one more use for this macro but its a bit more complex as it involves multiple columns. But I dont want to scare you guys..

Regards
Tinku

[Anne Troy]

LOL...you can't scare us!!! ROFL!!!
Ok. I'm gonna do it on your sample.

[sandam]

I fiddled like i said I would and this is what I came up with. It takes each segment as its concatenated and inserts a "," as a delimiter as well as seeing what the biggest number of rows is (this is NB for second step). then in the second step I inserted a number of columns based on the max number of parts moved up. On the sample it was 5 or 6 rows. Then taking these comma delimited strings in the single cell, i split the string, pasted each piece in a new column and the deleted the column with the concatenated string.

here's the code to do it. HTH

Andrew;?
[vba]
Option Explicit
'this variable records the largest amount of
'rows created by the export
Private MaxRows As Integer

'this sub compares the row count from FixFormat1
'to the max of the last one
Private Sub CheckMax(sNum As Integer)
If sNum > MaxRows Then
MaxRows = sNum
End If
End Sub

'This is the sub that takes the "," delimited cell value
',splits it, and pastes the parts into seperate columns
Private Sub FixFormat2()
Dim I As Integer, J As Integer
Dim LastRow As Long
Dim temp() As String

Range("C1").Select
For I = 1 To MaxRows
'inserting extra columns
Selection.EntireColumn.Insert
Next I
'working form the bottom row again
LastRow = Range("B65536").End(xlUp).Row
For I = LastRow To 2 Step -1
'split the cell value
temp = Split(Range("B" & I).Text, ",")
'paste it into the columns
For J = 1 To UBound(temp) + 1
Range(Chr(Asc("B") + J) & I).Value = temp(J - 1)
Next J
Next I
'delete the un-needed column with the
'concatenated string
Range("B1").Select
Selection.EntireColumn.Delete
End Sub

'sub to concatenate the rows of one column
Private Sub FixFormat1()
Dim LastRow As Long
Dim x As Long
Dim NumRows As Integer

NumRows = 1
LastRow = Range("B65536").End(xlUp).Row
For x = LastRow To 2 Step -1
If Range("A" & x).Value = "" Then
'Selection.Insert Shift:=xlToRight
Range("B" & x - 1).Value = Range("B" & x - 1).Value & "," & Range("B" & _
x).Value
Range("B" & x).EntireRow.Delete
NumRows = NumRows + 1
Else
CheckMax (NumRows)
NumRows = 1
End If
Next x
End Sub

Sub FixSheetFormat()
'this sub just runs the two seperate subs and
'sets the initial value of maxrows
MaxRows = 1
FixFormat1
FixFormat2
End Sub
[/vba]

P.s I think i could probable get this working for multiple columns of data. Just give me a shout and i'll fiddle again