Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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. "AndyC812" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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" wrote: 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. "AndyC812" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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" wrote: 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" wrote: 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. "AndyC812" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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" wrote: 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" wrote: 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" wrote: 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. "AndyC812" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. "AndyC812" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting around 255 Column limit
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 "AndyC812" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. "AndyC812" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column Limit | Excel Programming | |||
How do I limit sorting a column to the column? | Excel Worksheet Functions | |||
Limit Row and Column Highlight | Excel Programming | |||
Will the column limit be over 256? | Excel Discussion (Misc queries) | |||
Column Limit | Excel Programming |