PDA

View Full Version : Help with a mailmerge list



Tinku
03-18-2005, 09:27 AM
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
03-18-2005, 09:42 AM
Quick question, where is your data coming from? Is it another XL file?

Tinku
03-18-2005, 10:02 AM
Oops Sorry about that.. I have exported the data into an excel file..

Regards

Tinku

sandam
03-18-2005, 10:43 AM
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
03-18-2005, 11:02 AM
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
03-18-2005, 11:08 AM
If you'd prefer, Tinku, you can zip and upload a sample file. :yes Just use the Manage Attachments button in the Reply screen (cannot use Quick Reply).

Tinku
03-18-2005, 11:17 AM
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..:dunno

Any ideas ?

Regards
Tinku

Zack Barresse
03-18-2005, 11:20 AM
email it to me at ..

firefytr at vbaexpress dot com

Attach as any type, I can zip.

Tinku
03-18-2005, 11:26 AM
I u/l it here

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

Please see if it works

Regards
Tinku

Anne Troy
03-18-2005, 11:40 AM
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
03-18-2005, 11:43 AM
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
03-18-2005, 11:46 AM
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
03-18-2005, 12:20 PM
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||
-------------------------------------------------


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


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
03-18-2005, 12:25 PM
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
03-18-2005, 12:58 PM
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
03-18-2005, 01:23 PM
I'll continue to work on this , its little more perplexing than i first thought.

Anne Troy
03-18-2005, 01:34 PM
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
03-18-2005, 02:13 PM
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.. :devil:

Regards
Tinku

Anne Troy
03-18-2005, 02:20 PM
LOL...you can't scare us!!! ROFL!!!
Ok. I'm gonna do it on your sample.

sandam
03-20-2005, 04:43 PM
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;?

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


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 :)

Tinku
03-21-2005, 11:02 AM
Hi Sandam

This works like a charm.. Thank you so much..this forum rocks..I have another spreadsheet but it has 3 columns instead of one as in this case.. any ideas on how I could use the above code and quickly change it for use with this new sp/sh.

Regards

Tinku

sandam
03-21-2005, 11:14 AM
I have an idea or two. Could you upload a sample of the new format? (with the three columns that is).

Andrew;?

Tinku
03-21-2005, 11:33 AM
Hi Sandam

I have u/l a sample..this time it has more rows .. I want the last 3 columns
to be spread out into a single row. Thanks m8 for all the :help and efforts..

Regards
Tinku

sandam
03-21-2005, 12:40 PM
This will concatenate each of the last three rows but I'm having a little trouble adapting the spreading of it out over the columns. I'll have a sleep on it and see what I can devise to spread each of the columns.


Private Sub FixFormat1()
Dim LastRow As Long
Dim x As Long
Dim NumRows1 As Integer
Dim NumRows2 As Integer
Dim NumRows3 As Integer

NumRows1 = 1
NumRows2 = 1
NumRows3 = 1
LastRow = Range("AM65536").End(xlUp).Row
For x = LastRow To 2 Step -1
If Range("AH" & x).Value = "" Then
If Range("AK" & x).Value <> "" Then
Range("AK" & x - 1).Value = Range("AK" & x - 1).Value & "," & _
Range("AK" & x).Value
NumRows1 = NumRows1 + 1
Else
CheckMax MaxRows1, NumRows1
NumRows1 = 1
End If
If Range("AL" & x).Value <> "" Then
Range("AL" & x - 1).Value = Range("AL" & x - 1).Value & "," & _
Range("AL" & x).Value
NumRows2 = NumRows2 + 1
Else
CheckMax MaxRows2, NumRows2
NumRows2 = 1
End If
If Range("AM" & x).Value <> "" Then
Range("AM" & x - 1).Value = Range("AM" & x - 1).Value & "," & _
Range("AM" & x).Value
NumRows3 = NumRows3 + 1
Else
CheckMax MaxRows3, NumRows3
NumRows3 = 1
End If
Range("AH" & x).EntireRow.Delete
End If
Next x
End Sub

Tinku
03-21-2005, 01:20 PM
Thank you so much Sandam.. I will give this a try and wait for your updates. I hope to start studying VBA soon so that I dont have to bother you as much..hopefully very soon ...:thumb

Regards
Tinku

sandam
03-21-2005, 05:54 PM
Your no bother tinku. i have always meant to learn more about Excel VBA and helping you, helps me. i'm off to bed now - its 1am here. have a good evening. :)

sandam
03-22-2005, 04:42 PM
I'm loathe to admit this but I can seem to get the second half working for more than one column. it might be that I'm not going about it the right way but I'll give it another bash tomorrow. I only hope I can get it wokring for you. :)

Tinku
03-23-2005, 10:00 AM
Hi Sandam

I dont want you to trouble urself too much.. you have already done enuf.
Lets consider this project complete. What I am planning on doing with the new file is split it into 3 diff files and use your earlier code and then import all the 3 files into access and create a simple inner join.
Thanks again..:clap: for all your hard work..

Regards
Tinku

PS : Is there a simple way to chk if a complete row is empty and then delete it ?

Ken Puls
03-24-2005, 10:11 AM
PS : Is there a simple way to chk if a complete row is empty and then delete it ?

This question has been split from this thread, and is being addressed here (http://www.vbaexpress.com/forum/showthread.php?t=2482) since it is a separate issue. :)