|
How to generate a text file from Excel using a macro or script
dear Ron,
Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
How to generate a text file from Excel using a macro or script
This will open the file after it create it
Is that what you want ? Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum Shell "notepad.exe Test.txt", vbNormalFocus End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... dear Ron, Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
How to generate a text file from Excel using a macro or script
Dear Ron, ooops! Thanks a lot! I got what I wanted! U made my life easy! Thank you very much again! Frank "Ron de Bruin" wrote: This will open the file after it create it Is that what you want ? Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum Shell "notepad.exe Test.txt", vbNormalFocus End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... dear Ron, Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
How to generate a text file from Excel using a macro or script
Daer Ron,
Your code solves my problem but the thing is it prints only the first row of the sheet how do I print many rows? Please reply! Thanks! -Frank- "Frank" wrote: Dear Ron, ooops! Thanks a lot! I got what I wanted! U made my life easy! Thank you very much again! Frank "Ron de Bruin" wrote: This will open the file after it create it Is that what you want ? Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum Shell "notepad.exe Test.txt", vbNormalFocus End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... dear Ron, Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
How to generate a text file from Excel using a macro or script
Dear Ron,
Sory I was confused! Your code exactly way the I want. And sory previouse message was a mistake it prints the whole sheet! But now my problem is, If I want to modify the code to get da text file with the exact cell spaces, how do i do that? Now da model acts like this if I take an example, Price,Qty,Total 2,4,8 5,3,15 But what I want is this, Price,Qty,Total 2, 4, 8 5, 3, 15 Is this possible? Using tab instead "," doesn't solve the problem Thanks for your kindness! Hope you will reply me fast! Thanks a lot! -Frank- "Frank" wrote: Daer Ron, Your code solves my problem but the thing is it prints only the first row of the sheet how do I print many rows? Please reply! Thanks! -Frank- "Frank" wrote: Dear Ron, ooops! Thanks a lot! I got what I wanted! U made my life easy! Thank you very much again! Frank "Ron de Bruin" wrote: This will open the file after it create it Is that what you want ? Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum Shell "notepad.exe Test.txt", vbNormalFocus End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... dear Ron, Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
How to generate a text file from Excel using a macro or script
Dear Ron,
I am sorry for sending so many posts! But now I have solved the problem as you have instructed me in the previous post I used vbTab command. and that workd! Hooray! I am very happy thanks a lot! I thank both Ron and Joel! Thank you again for spending your valuble time on this! Keep up your good service! -Frank- "Frank" wrote: Dear Ron, Sory I was confused! Your code exactly way the I want. And sory previouse message was a mistake it prints the whole sheet! But now my problem is, If I want to modify the code to get da text file with the exact cell spaces, how do i do that? Now da model acts like this if I take an example, Price,Qty,Total 2,4,8 5,3,15 But what I want is this, Price,Qty,Total 2, 4, 8 5, 3, 15 Is this possible? Using tab instead "," doesn't solve the problem Thanks for your kindness! Hope you will reply me fast! Thanks a lot! -Frank- "Frank" wrote: Daer Ron, Your code solves my problem but the thing is it prints only the first row of the sheet how do I print many rows? Please reply! Thanks! -Frank- "Frank" wrote: Dear Ron, ooops! Thanks a lot! I got what I wanted! U made my life easy! Thank you very much again! Frank "Ron de Bruin" wrote: This will open the file after it create it Is that what you want ? Public Sub TextNoModification() Const DELIMITER As String = "," 'or "|", vbTab, etc. Dim myRecord As Range Dim myField As Range Dim nFileNum As Long Dim sOut As String nFileNum = FreeFile Open "Test.txt" For Output As #nFileNum For Each myRecord In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With myRecord For Each myField In Range(.Cells(1), _ Cells(.Row, Columns.Count).End(xlToLeft)) sOut = sOut & DELIMITER & myField.Text Next myField Print #nFileNum, Mid(sOut, 2) sOut = Empty End With Next myRecord Close #nFileNum Shell "notepad.exe Test.txt", vbNormalFocus End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... dear Ron, Thanks a lot for your guiding I went through the page you showed me! And now I know where to copy and paste! but I neet to create a button in da excel sheet and then get da text data file open when clicked. I know to create a button on the excel sheet but I am scared to mention I still have problems with the code! Can U kindly help me with this? Thanks! "Ron de Bruin" wrote: I answer this in your other thread Please post in one group http://www.mcgimpsey.com/excel/textfiles.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Frank" wrote in message ... I need to know the way of generating a text file from data availbabe in an Excel spreadsheet either using a Macro or a Script! Asume if I have some data in few cells such as C5, C6 and C7 then if I have a simple formula like Sum in another cell like C8 to get the total of the above mentioned cells. How do I display the values in those cells in a text file generated by a macro or script? Please be kind enough to reply me if you know this! Thanks a lot! -Frank- |
| All times are GMT +1. The time now is 12:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com