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!

"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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