How to merge 500 columns of data

[chizz]

I posted earlier and was not too clear on what I was trying t
accomplish. I have two excel files set up and I am currently using 25
of the 255 columns of each one. There is only one row of data, but
need a way to get 500 columns of data into a single spreadsheet so tha
i can merge all 500 peices of data with my word doc at once. I a
currently using Office 2000. Any help is greatly appreciated

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Unfortunately, I believe that no matter which way you turn, you will run
into that 255 field/column limit.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP

 

[Avi]

Hi

Does the data have to be in Excel

I do a mailmerge with over 500 merge fields using a delimited word file as the merge source

Note: there is a bug in Word 2000/XP which causes Word to crash when you have a large number (I do not recall what the number is) of merge fields and use the "normal" mail merge wizzard functions. You can still use the older (query options). This has been fixed in Word 2003

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QXZp?=,

Note: there is a bug in Word 2000/XP which causes Word to crash when you have a large

number (I do not recall what the number is) of merge fields and use the "normal" mail merge
wizzard functions. You can still use the older (query options). This has been fixed in Word
2003286 fields, and it comes from trying to display them in the Recipients dialog box

I do a mailmerge with over 500 merge fields using a delimited word file as the merge source.

I considered mentioning your suggestion, but then couldn't think of any reasonably easy way to
put the two Excel files together "side-by-side" in a plain text file. Do you have any
suggestions on that score?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply in the
newsgroup and not by e-mail

 

[Doug Robbins - Word MVP - DELETE UPPERCASE CHARACT]

Hi Cindy,

Something like this should do it (a modification of Ibby's code at
http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm):

Dim db1 As DAO.Database, db2 As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim NoOfRecords As Long, i As Long, j As Long, flds1 As Long, flds2 As
Long
Dim Target As Document
' Open the databases
Set db1 = OpenDatabase("D:\Excel\Book1.xls", False, False, "Excel 8.0")
Set db2 = OpenDatabase("D:\Excel\Book2.xls", False, False, "Excel 8.0")
' Retrieve the recordsets Database1 and Database2 are named ranges in
Book1 and Book2 respectively
' The first row of the named range will not be brought in by this
procedure. There for if it is desired to bring in
' the field names, insert a blank row before the row in which the
fieldnames appear and include that blank row
' in the named range.
Set rs1 = db1.OpenRecordset("SELECT * FROM Database1")
Set rs2 = db2.OpenRecordset("SELECT * FROM Database2")
' Determine the number of retrieved records
With rs1
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
flds1 = rs1.Fields.Count
flds2 = rs2.Fields.Count

' Create a document into which the information will be inserted
Set Target = Documents.Add

For i = 1 To NoOfRecords
For j = 0 To flds1 - 1
Target.Range.InsertAfter rs1.Fields(j) & ","
Next j
For j = 0 To flds2 - 2
Target.Range.InsertAfter rs2.Fields(j) & ","
Next j
Target.Range.InsertAfter rs2.Fields(j) & vbCr
rs1.MoveNext
rs2.MoveNext
Next i

' Clean up
rs1.Close
rs2.Close
db1.Close
db2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db1 = Nothing
Set db2 = Nothing


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP