| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Data to Text file with fix format
Hello All,
I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
Saved from a previous post:
You could concatenate the cell values into another column: =LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00") (You'll have to modify it to match what you want.) Drag it down the column to get all that fixed width stuff. Then I'd copy and paste to notepad and save from there. Once I figured out that ugly formula, I kept it and just unhide that column when I wanted to export the data. If that doesn't work for you, maybe you could do it with a macro. Here's a link that provides a macro: http://google.com/groups?threadm=015...0a% 40phx.gbl Rushna wrote: Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
Can you clarify a couple of things? You say your data is in the range
D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
You description has some wholes. I did the best I could. The code may
require minor changes. Sub WriteText() Const myFileName = "c:\temp\myfile.csv" Const ForReading = 1, ForWriting = 2, _ ForAppending = 3 fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Text Files (*.txt), *.txt") If fileSaveName = False Then MsgBox ("Cannot Save File - exiting Sub") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile _ (fileSaveName, True) LastRow = Range("C" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow MyNumber = Format(Range("C" & RowCount).Value, "####") outputLine = MyNumber & " " MyNumber = Format(Range("F" & RowCount).Value, " ########") outputLine = outputLine & MyNumber MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyNumber = Format(Abs(Range("K" & RowCount).Value), _ "#000000000000000.00") outputLine = outputLine & MyNumber outputLine = outputLine & Range("L" & RowCount).Value & " " If Range("Q" & RowCount) < "" Then outputLine = outputLine & Range("Q" & RowCount) & _ String(10 - Len(Range("Q" & RowCount)), " ") End If f.writeline outputLine Next RowCount f.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) €“ to be exported (total 9 digits) with trailing blanks (eg. 1234 €“ should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) €“ to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 €“ to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 €“ to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) €“ to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L €“ to be exported with two trailing blanks (currently EUR €“ should display as EUR(blank)(blank). Col Q €“ to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
Hi Rick,
Thanks for the prompt reply. Sorry. Data starts from Col C as you have pointed out. It was my mistake. Since we import the data, other columns are blank. I need to only export the columns (digit count shown) as follows: Col C 1-9 5894 – should display as 5894(and 5 spaces) – total 9 Col F 10-19 Invoice number 12345678 – should display as 001234567 – total 10 Col H 20-27 Invoice date 21.02.2008 should display as 20080221 – total 8 Col I 28-35 Payment date 21.02.2008 should display as 20080221 – total 8 Col K 36-50 Payment amount, leading zeros, 2 decimals, no decimal sign – total 15 Col L 51-55 Currency code – EUR, should display EUR(and 2 spaces) – total 5 Col Q 56-66 BIC code bank, with or without branch – total 11 Thanks On Jul 20, 8:34*pm, "Rick Rothstein \(MVP - VB\)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
On Jul 20, 9:23*pm, Joel wrote:
You description has some wholes. *I did the best I could. *The code may require minor changes. Sub WriteText() Const myFileName = "c:\temp\myfile.csv" Const ForReading = 1, ForWriting = 2, _ * *ForAppending = 3 fileSaveName = Application.GetSaveAsFilename( _ * * fileFilter:="Text Files (*.txt), *.txt") If fileSaveName = False Then * * MsgBox ("Cannot Save File - exiting Sub") * * Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile _ * *(fileSaveName, True) LastRow = Range("C" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow * *MyNumber = Format(Range("C" & RowCount).Value, "####") * *outputLine = MyNumber & " * * " * *MyNumber = Format(Range("F" & RowCount).Value, " *########") * *outputLine = outputLine & MyNumber * *MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD") * *outputLine = outputLine & MyNumber * *MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD") * *outputLine = outputLine & MyNumber * *MyNumber = Format(Abs(Range("K" & RowCount).Value), _ * * * "#000000000000000.00") * *outputLine = outputLine & MyNumber * *outputLine = outputLine & Range("L" & RowCount).Value & " *" * *If Range("Q" & RowCount) < "" Then * * * outputLine = outputLine & Range("Q" & RowCount) & _ * * * * *String(10 - Len(Range("Q" & RowCount)), " ") * *End If * *f.writeline outputLine Next RowCount f.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message .... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna- Hide quoted text - - Show quoted text - Hello Joel When I run your macro it gives me "Run time error-13. Type Mismatch" Please help Thanks a lot |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
Okay, give the following (untested, but it should work fine) macro a try.
Just assign the appropriate worksheet reference in the With statement and assign the filename (with its path) in the variable named FileNameAndPath (replacing my example text, of course). Sub WriteDataOut() Dim X As Long Dim FF As Long Dim LastRow As Long Dim Record As String Dim TotalFile As String Dim FileNameAndPath As String FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row For X = 10 To LastRow Record = Space$(66) Mid$(Record, 1) = .Cells(X, "C").Value Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd") Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd") Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value), "000000000000000") Mid$(Record, 51) = .Cells(X, "L").Value Mid$(Record, 56) = .Cells(X, "Q").Value TotalFile = TotalFile & vbCrLf & Record Next FF = FreeFile Open FileNameAndPath For Output As #FF Print #FF, Record Close #FF End With End Sub Rick "Rushna" wrote in message ... On Jul 20, 9:23 pm, Joel wrote: You description has some wholes. I did the best I could. The code may require minor changes. Sub WriteText() Const myFileName = "c:\temp\myfile.csv" Const ForReading = 1, ForWriting = 2, _ ForAppending = 3 fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Text Files (*.txt), *.txt") If fileSaveName = False Then MsgBox ("Cannot Save File - exiting Sub") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile _ (fileSaveName, True) LastRow = Range("C" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow MyNumber = Format(Range("C" & RowCount).Value, "####") outputLine = MyNumber & " " MyNumber = Format(Range("F" & RowCount).Value, " ########") outputLine = outputLine & MyNumber MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyNumber = Format(Abs(Range("K" & RowCount).Value), _ "#000000000000000.00") outputLine = outputLine & MyNumber outputLine = outputLine & Range("L" & RowCount).Value & " " If Range("Q" & RowCount) < "" Then outputLine = outputLine & Range("Q" & RowCount) & _ String(10 - Len(Range("Q" & RowCount)), " ") End If f.writeline outputLine Next RowCount f.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna- Hide quoted text - - Show quoted text - Hello Joel When I run your macro it gives me "Run time error-13. Type Mismatch" Please help Thanks a lot |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
I see my newsreader wrapped one of the statements, so I am guessing yours
did too. Here is the code again, this time using a line continuation so that you can simply copy/paste the code in... Sub WriteDataOut() Dim X As Long Dim FF As Long Dim LastRow As Long Dim Record As String Dim TotalFile As String Dim FileNameAndPath As String FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row For X = 10 To LastRow Record = Space$(66) Mid$(Record, 1) = .Cells(X, "C").Value Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd") Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd") Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value) _ , "000000000000000") Mid$(Record, 51) = .Cells(X, "L").Value Mid$(Record, 56) = .Cells(X, "Q").Value TotalFile = TotalFile & vbCrLf & Record Next FF = FreeFile Open FileNameAndPath For Output As #FF Print #FF, Record Close #FF End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in message ... Okay, give the following (untested, but it should work fine) macro a try. Just assign the appropriate worksheet reference in the With statement and assign the filename (with its path) in the variable named FileNameAndPath (replacing my example text, of course). Sub WriteDataOut() Dim X As Long Dim FF As Long Dim LastRow As Long Dim Record As String Dim TotalFile As String Dim FileNameAndPath As String FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row For X = 10 To LastRow Record = Space$(66) Mid$(Record, 1) = .Cells(X, "C").Value Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd") Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd") Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value), "000000000000000") Mid$(Record, 51) = .Cells(X, "L").Value Mid$(Record, 56) = .Cells(X, "Q").Value TotalFile = TotalFile & vbCrLf & Record Next FF = FreeFile Open FileNameAndPath For Output As #FF Print #FF, Record Close #FF End With End Sub Rick "Rushna" wrote in message ... On Jul 20, 9:23 pm, Joel wrote: You description has some wholes. I did the best I could. The code may require minor changes. Sub WriteText() Const myFileName = "c:\temp\myfile.csv" Const ForReading = 1, ForWriting = 2, _ ForAppending = 3 fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Text Files (*.txt), *.txt") If fileSaveName = False Then MsgBox ("Cannot Save File - exiting Sub") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile _ (fileSaveName, True) LastRow = Range("C" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow MyNumber = Format(Range("C" & RowCount).Value, "####") outputLine = MyNumber & " " MyNumber = Format(Range("F" & RowCount).Value, " ########") outputLine = outputLine & MyNumber MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyNumber = Format(Abs(Range("K" & RowCount).Value), _ "#000000000000000.00") outputLine = outputLine & MyNumber outputLine = outputLine & Range("L" & RowCount).Value & " " If Range("Q" & RowCount) < "" Then outputLine = outputLine & Range("Q" & RowCount) & _ String(10 - Len(Range("Q" & RowCount)), " ") End If f.writeline outputLine Next RowCount f.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna- Hide quoted text - - Show quoted text - Hello Joel When I run your macro it gives me "Run time error-13. Type Mismatch" Please help Thanks a lot |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
On Jul 20, 10:27*pm, "Rick Rothstein \(MVP - VB\)"
wrote: I see my newsreader wrapped one of the statements, so I am guessing yours did too. Here is the code again, this time using a line continuation so that you can simply copy/paste the code in... Sub WriteDataOut() * Dim X As Long * Dim FF As Long * Dim LastRow As Long * Dim Record As String * Dim TotalFile As String * Dim FileNameAndPath As String * FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" * With Worksheets("Sheet1") * * LastRow = .Cells(Rows.Count, "C").End(xlUp).Row * * For X = 10 To LastRow * * * Record = Space$(66) * * * Mid$(Record, 1) = .Cells(X, "C").Value * * * Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") * * * Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd") * * * Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd") * * * Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value) _ * * * * * * * * * * * * * * * * *, "000000000000000") * * * Mid$(Record, 51) = .Cells(X, "L").Value * * * Mid$(Record, 56) = .Cells(X, "Q").Value * * * TotalFile = TotalFile & vbCrLf & Record * * Next * * FF = FreeFile * * Open FileNameAndPath For Output As #FF * * Print #FF, Record * * Close #FF * End With End Sub Rick "Rick Rothstein (MVP - VB)" wrote in l... Okay, give the following (untested, but it should work fine) macro a try. Just assign the appropriate worksheet reference in the With statement and assign the filename (with its path) in the variable named FileNameAndPath (replacing my example text, of course). Sub WriteDataOut() *Dim X As Long *Dim FF As Long *Dim LastRow As Long *Dim Record As String *Dim TotalFile As String *Dim FileNameAndPath As String *FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" *With Worksheets("Sheet1") * *LastRow = .Cells(Rows.Count, "C").End(xlUp).Row * *For X = 10 To LastRow * * *Record = Space$(66) * * *Mid$(Record, 1) = .Cells(X, "C").Value * * *Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") * * *Mid$(Record, 20) = Format$(.Cells(X, "H").Value, "yyyymmdd") * * *Mid$(Record, 28) = Format$(.Cells(X, "I").Value, "yyyymmdd") * * *Mid$(Record, 36) = Format$(Abs(.Cells(X, "K").Value), "000000000000000") * * *Mid$(Record, 51) = .Cells(X, "L").Value * * *Mid$(Record, 56) = .Cells(X, "Q").Value * * *TotalFile = TotalFile & vbCrLf & Record * *Next * *FF = FreeFile * *Open FileNameAndPath For Output As #FF * *Print #FF, Record * *Close #FF *End With End Sub Rick "Rushna" wrote in message ... On Jul 20, 9:23 pm, Joel wrote: You description has some wholes. I did the best I could. The code may require minor changes. Sub WriteText() Const myFileName = "c:\temp\myfile.csv" Const ForReading = 1, ForWriting = 2, _ ForAppending = 3 fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Text Files (*.txt), *.txt") If fileSaveName = False Then MsgBox ("Cannot Save File - exiting Sub") Exit Sub End If Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.CreateTextFile _ (fileSaveName, True) LastRow = Range("C" & Rows.Count).End(xlUp).Row For RowCount = 1 To LastRow MyNumber = Format(Range("C" & RowCount).Value, "####") outputLine = MyNumber & " " MyNumber = Format(Range("F" & RowCount).Value, " ########") outputLine = outputLine & MyNumber MyDate = Format(Range("H" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyDate = Format(Range("I" & RowCount).Value, "YYYYMMDD") outputLine = outputLine & MyNumber MyNumber = Format(Abs(Range("K" & RowCount).Value), _ "#000000000000000.00") outputLine = outputLine & MyNumber outputLine = outputLine & Range("L" & RowCount).Value & " " If Range("Q" & RowCount) < "" Then outputLine = outputLine & Range("Q" & RowCount) & _ String(10 - Len(Range("Q" & RowCount)), " ") End If f.writeline outputLine Next RowCount f.Close End Sub "Rick Rothstein (MVP - VB)" wrote: Can you clarify a couple of things? You say your data is in the range D10:Q(20000+rows), but your field description shows the first column as C, not D... which is it? If C is correct, what about column's D and E; if C should have been D, then what about column E? I added up all the "data" you laid out and get 63 characters, not the 66 you said would be there.... if 66 is correct, and assuming I did not miscount, where do the additional 3 spaces go... in front or at the back of the 66 character record? Rick "Rushna" wrote in message ... Hello All, I am using Excel 2003 and have data in Range D10:Q(20,000+ rows). I wish to export the all active rows in the range in the Excel file to a text file (Notepad). Details are as follow: Col C (currently 4 digits) – to be exported (total 9 digits) with trailing blanks (eg. 1234 – should export as 1234(blank)(blank)(blank) (blank)(blank) Col F (currently 8 digits) – to be exported (total 10 digits) with two leading blanks (eg 12345678 should show as 0012345678) Col H (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col I (currently 21.02.2008 – to be exported as yyyymmdd (eg 20080221) Col K (currently -43,413.20) – to be exported as total 15 digits with leading zeros, 2 decimal place, no decimal signs (eg -43,413.20 should display as 000000004341320 and -1,234.23 should display as 000000000123423 etc) Col L – to be exported with two trailing blanks (currently EUR – should display as EUR(blank)(blank). Col Q – to be exported as ten digits (eg. AABBCCDDXXX). If the cell is blank nothing should be exported The output on each line in the text file should be a total width of 66 (including spaces) from various Columns in Excel as explained above and displayed as follows: 1234(blank)(blank)(blank)(blank) (blank)00123456782008022120080221000000004341320EU R(blank) (blank)AABBCCDDXXX (this counts to 66 including blank spaces) Thanks in advance. Rushna- Hide quoted text - - Show quoted text - Hello Joel When I run your macro it gives me "Run time error-13. Type Mismatch" Please help Thanks a lot- Hide quoted text - - Show quoted text - Hi Rick, Your macro exported only one record i.e the last record as follows. 5894 003000096215.03.2015.03.20000000000049184USD AABBCCDDXXX Please note the following errors 1) Col H and I - Date is exported as 15.03.20 (please note that the year is 2006) - it should be displayed as 20060315 in both case. 2) Col K (value is -49,183.80) the output is displayed as 000000000049184 - it should be displayed as 000000004318380 (i.e leading zeros, two decimal places without any decimal signs) Please help |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
Your macro exported only one record i.e the last record as follows.
5894 003000096215.03.2015.03.20000000000049184USD AABBCCDDXXX Please note the following errors 1) Col H and I - Date is exported as 15.03.20 (please note that the year is 2006) - it should be displayed as 20060315 in both case. 2) Col K (value is -49,183.80) the output is displayed as 000000000049184 - it should be displayed as 000000004318380 (i.e leading zeros, two decimal places without any decimal signs) Argh! The single record print out was because I specified the wrong variable in the Print statement. The other errors were because of my misreading what you wrote. Give this revised code a try... Sub WriteDataOut() Dim X As Long Dim FF As Long Dim LastRow As Long Dim Dte As String Dim Record As String Dim TotalFile As String Dim FileNameAndPath As String FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row For X = 10 To LastRow Record = Space$(66) Mid$(Record, 1) = .Cells(X, "C").Value Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") Dte = .Cells(X, "H").Value Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) Dte = .Cells(X, "H").Value Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _ "000000000000000") Mid$(Record, 51) = .Cells(X, "L").Value Mid$(Record, 56) = .Cells(X, "Q").Value TotalFile = TotalFile & vbCrLf & Record Next FF = FreeFile Open FileNameAndPath For Output As #FF Print #FF, TotalFile Close #FF End With End Sub Rick |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
On Jul 21, 12:20*am, "Rick Rothstein \(MVP - VB\)"
wrote: Your macro exported only one record i.e the last record as follows. 5894 * * 003000096215.03.2015.03.20000000000049184USD *AABBCCDDXXX Please note the following errors 1) Col H and I - Date is exported as 15.03.20 (please note that the year is 2006) - it should be displayed as 20060315 in both case. 2) Col K (value is -49,183.80) the output is displayed as 000000000049184 - it should be displayed as 000000004318380 (i.e leading zeros, two decimal places without any decimal signs) Argh! The single record print out was because I specified the wrong variable in the Print statement. The other errors were because of my misreading what you wrote. Give this revised code a try... Sub WriteDataOut() * Dim X As Long * Dim FF As Long * Dim LastRow As Long * Dim Dte As String * Dim Record As String * Dim TotalFile As String * Dim FileNameAndPath As String * FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" * With Worksheets("Sheet1") * * LastRow = .Cells(Rows.Count, "C").End(xlUp).Row * * For X = 10 To LastRow * * * Record = Space$(66) * * * Mid$(Record, 1) = .Cells(X, "C").Value * * * Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") * * * Dte = .Cells(X, "H").Value * * * Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) * * * Dte = .Cells(X, "H").Value * * * Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) * * * Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _ * * * * * * * * * * * * * * * * * * * * * * * "000000000000000") * * * Mid$(Record, 51) = .Cells(X, "L").Value * * * Mid$(Record, 56) = .Cells(X, "Q").Value * * * TotalFile = TotalFile & vbCrLf & Record * * Next * * FF = FreeFile * * Open FileNameAndPath For Output As #FF * * Print #FF, TotalFile * * Close #FF * End With End Sub Rick Works perfect. Thanks a lot for your help. Rushna. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Export Data to Text file with fix format
On Jul 21, 7:57*pm, Rushna wrote:
On Jul 21, 12:20*am, "Rick Rothstein \(MVP - VB\)" wrote: Your macro exported only one record i.e the last record as follows. 5894 * * 003000096215.03.2015.03.20000000000049184USD *AABBCCDDXXX Please note the following errors 1) Col H and I - Date is exported as 15.03.20 (please note that the year is 2006) - it should be displayed as 20060315 in both case. 2) Col K (value is -49,183.80) the output is displayed as 000000000049184 - it should be displayed as 000000004318380 (i.e leading zeros, two decimal places without any decimal signs) Argh! The single record print out was because I specified the wrong variable in the Print statement. The other errors were because of my misreading what you wrote. Give this revised code a try... Sub WriteDataOut() * Dim X As Long * Dim FF As Long * Dim LastRow As Long * Dim Dte As String * Dim Record As String * Dim TotalFile As String * Dim FileNameAndPath As String * FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" * With Worksheets("Sheet1") * * LastRow = .Cells(Rows.Count, "C").End(xlUp).Row * * For X = 10 To LastRow * * * Record = Space$(66) * * * Mid$(Record, 1) = .Cells(X, "C").Value * * * Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") * * * Dte = .Cells(X, "H").Value * * * Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) * * * Dte = .Cells(X, "H").Value * * * Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) * * * Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _ * * * * * * * * * * * * * * * * * * * * * * * "000000000000000") * * * Mid$(Record, 51) = .Cells(X, "L").Value * * * Mid$(Record, 56) = .Cells(X, "Q").Value * * * TotalFile = TotalFile & vbCrLf & Record * * Next * * FF = FreeFile * * Open FileNameAndPath For Output As #FF * * Print #FF, TotalFile * * Close #FF * End With End Sub Rick Works perfect. Thanks a lot for your help. Rushna.- Hide quoted text - - Show quoted text - Hello All, The macro below provided by Mr. Rick Rothstein works perfect. It export the worksheet to a text file. I wish to change it further to suit to my needs. Sub WriteDataOut() Dim X As Long Dim FF As Long Dim LastRow As Long Dim Dte As String Dim Record As String Dim TotalFile As String Dim FileNameAndPath As String FileNameAndPath = "c:\Dir1\Dir2\etc\FileName.txt" With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "C").End(xlUp).Row For X = 10 To LastRow Record = Space$(66) Mid$(Record, 1) = .Cells(X, "C").Value Mid$(Record, 10) = Format$(.Cells(X, "F").Value, "0000000000") Dte = .Cells(X, "H").Value Mid$(Record, 20) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) Dte = .Cells(X, "H").Value Mid$(Record, 28) = Right$(Dte, 4) & Mid$(Dte, 4, 2) & Left$(Dte, 2) Mid$(Record, 36) = Format$(100 * Abs(.Cells(X, "K").Value), _ "000000000000000") Mid$(Record, 51) = .Cells(X, "L").Value Mid$(Record, 56) = .Cells(X, "Q").Value TotalFile = TotalFile & vbCrLf & Record Next FF = FreeFile Open FileNameAndPath For Output As #FF Print #FF, TotalFile Close #FF End With End Sub Changes required: The file should be saved in the given Folder Path using the Cell Value (E5) with date and time stamped e.g if cell value (E5) in the excel file is 1234 then the file should be saved as "1234 (date) (time)".txt . If the value in E5 is 3456 then it should be saved as "3456 (date) (time).txt" At present it saves as filename.txt and it overwirtes the previous version too. I need to have all the old files with date and time stamped for future references using the Cell Value in E5. Can someone help me please? Thanks in advance Rushna |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I export data in text format with minimum of spaces? | Excel Discussion (Misc queries) | |||
| How to make text data export to excel in text format. | Excel Programming | |||
| Open CSV file, format data and write output to a text file. | Excel Programming | |||
| How do I import text file, analyze data, export results, open next file | Excel Programming | |||
| Convert data and export to a text file | Excel Programming | |||
Linear Mode
