Getting around 255 Column limit

[AndyC812]

I have created an application in Excel that collects data for eventual
merging into a Word mail merge document. It is so large that I am running
out of columns in Excel for new merge fields (I am using 250 of possible
255). My question - Short of upgrading to Office 2007, is there a better way
to get my data fields from Excel into Word (CSV file, XML?) ? Can this be
automated (i.e., can I create a button with a macro behind it that will open
a Word merge document, and do a merge to new document)?

Any tips would be appreciated!

Thanks!
Andy

 

[Joel]

1) The first think to consider if you can transpose your columns and rows.
2) Split your data into multiple worksheets. You can easily write a macro
that creates CSV going across multiple sheets.
3) Put single entry on multiple rows.

 

[AndyC812]

If I transpose columns and rows, then won't Word have a problem merging the
data? It looks in the first row for the data headers.

I have considered splitting my data into multiple sheets depending on the
report I want to write, but that would make it harder for my users to run the
report. Unless there is a way to automate the running of the report and
merging the data.

Can you provide an example or link to an article on how to generate a CSV
from Excel???

Thanks!

 

[Joel]

All CSV i(Comma Sperated Values) s a text file with each field seperated by a
comma and a Return at the end of each line. You can use the SAVEAS feature
in excel and select CSV to save the file. or yo ucan use a macro like the
one below.

Sub WriteCSV()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = "," & Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
OutputLine = OutputLine & ","
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub

 

[AndyC812]

Wow, nice bit of code, not sure I understand how to adpt it for my use. Let
me ask a few questions. Is the CSV format one header, data pair per line or
does all of the data have to be in one long row? That is to say, like this:
field_name1,data1
field_name2,data2
....
field_name256,data256

Or like this:
field_name1,data1,field_name2,data2, ... ,field_name256,data256

What happens if you have commas or CR in your data (as I do)?

Taking it a step further, can I shoot the CSV file to Word and merge it to a
new document?

Thanks!

 

[AndyC812]

OK, did a quick test. I see now it's neither of these, it's like this:
fieldname1,fieldname2, ...fieldname256...
data1,data2,...,data256...

But the questions again then is what to do with data that contain CR and
commas?
Also, in order to get over 256 fields, I would have to store the data in two
or more worksheets. How do I get data from multiple sheets into one CSV?

Sorry for all the questions, I am a bit new at this.

 

[Joel]

CSV files are used for millions *billions) of different applications. First,
it is a text file which every programming language accepts. Second it has
not format except the commas and returns. It is often used to transfer data
from one application to another. You can read the file using any text editor
such as Notepad or Wordpad.


Sub WriteCSV()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
LastSheet = 2
For RowCount = 1 To LastRow
OutputLine = ""
For SheetCount = 1 To LastSheet
With Sheets(SheetCount)
If SheetCount = LastSheet Then
LastCol = .Cells(RowCount, Columns.Count) _
.End(xlToLeft).Column
Else
LastCol = 256
End If
For ColCount = 1 To LastCol
If OutputLine = "" Then
OutputLine = .Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & _
.Cells(RowCount, ColCount)
End If
Next ColCount
End With
Next SheetCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub

 

[AndyC812]

This looks great, Joel, thank you so much. Just a few thiings - What about
very long data fields that include commas and returns? Will this mess up the
CSV? Once I do write the CSV to a temp file, how do I then start a Word
document with the mail merge template and connect it to the CSV and do a
"Merge to New Document"? WIll this work in Windows (Office 2003 and 2007)
and MAC?

Thanks, again,
Andy

 

[Joel]

Some version of CSV allows double quotes around each data field that weill
ignore the commas between the double quotes. The real double quotes are
repeated twice. there are lots of different options you can use especially
if you write the code yourself.

I don't do a lot of Merge mail so you might want to post these questions as
a new posting.

 

[AndyC812]

OK, I will start another question here and in Word Mail Merge forum.
However, I am still trying to get your macro to work. I have rearranged by
data into columns, Col A is headers and Col B is value. This way I now have
over 65,000 possible data fields. I can't seem to be able to change your
code to fit that situation. Also, I can't figure out how to specify which
worksheet to get the data out of. It keeps trying to get the data from the
first worksheet no matter what I do.

Thanks,
Andy

 

[Joel]

Sub WriteCSV()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
LastSheet = 2

For SheetCount = 1 To LastSheet
With Sheets(SheetCount)
If SheetCount = LastSheet Then
LastCol = .Cells(RowCount, Columns.Count) _
.End(xlToLeft).Column
Else
LastCol = 256
End If
For ColCount = 1 To LastCol
OutputLine = ""
For RowCount = 1 To LastRow
If OutputLine = "" Then
OutputLine = .Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & _
.Cells(RowCount, ColCount)
End If
Next RowCount
tswrite.writeline OutputLine
Next ColCount
End With
Next SheetCount

tswrite.Close

Exit Sub
End Sub