Getting around 255 Column limit
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
|