| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The Print # command print not all rows from excel sheet to file
I wonder if someone can help me with this.
I try to read from a text file with this piece of code; Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop (This file is 110 lines long). And so far is all going well. But now I have to write all lines to another file (some lines are modified). Unfortunately stops the writing at line 100. (Some characters at the end of line 100 are also missing). The row counting is going on until the first empty row in the excel sheet. I herefor use this code; Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop I don't know why the Print #1-command suddenly stops writing to the file at line 100???? Can someone help me with this?? Thanks, Pieros. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
Pieros,
No reason why print should stop at 100 unless you tell it to stop. Maybe one of these ? - I suppose the Cells refer to the same worksheet in both routines, but it would be better the be clear. e.g. With Worksheet(1) Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel .Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar 'Notice the "." before the Cells above - Are you sure Cells(RowNdx, ColNdx) = "" does not evaluate to true at line 100 ? - You are resetting RowNdx=0 before the 2 loop ? -Are you sure all the data is correctly read first ? Also, it is not a good idea to hardcode file numbers. Use FreeFile instead. Check the Help NickHK "pieros" wrote in message ups.com... I wonder if someone can help me with this. I try to read from a text file with this piece of code; Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop (This file is 110 lines long). And so far is all going well. But now I have to write all lines to another file (some lines are modified). Unfortunately stops the writing at line 100. (Some characters at the end of line 100 are also missing). The row counting is going on until the first empty row in the excel sheet. I herefor use this code; Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop I don't know why the Print #1-command suddenly stops writing to the file at line 100???? Can someone help me with this?? Thanks, Pieros. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
NickHK, - The Cells refer indeed to the same worksheet in both routines. - I am sure Cells(RowNdx, ColNdx) = "" does evaluate TRUE at line 100. The first empty cell is A112 and the RowNdx stops counting here. So that's working correctly. - I am resetting the RowNdx before the second loop. - I am sure all the data is read correctly in the first column. I send you my total code. (This code is only a test. If this works fine I will go on for all my files. It is at the same time good for me to practice so I later can do more with VBA). Here is my code: Option Explicit '*********************** Programma schrijft alleen de 1e 100 regels naar het aangegeven file - de laatste regel niet helemaal compleet ??????? ** Sub read_file() Dim strTemp As String Dim RowNdx As Integer Dim ColNdx As Integer Dim Line1 As String RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Approved\Cop00004500\00004583_001_KART_KM9_V 1.xml" For Input As #1 Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop Line1 = Mid(Cells(1, 1), 3, 21) Cells(1, 1).Value = Line1 'schrijf regel naar Excel-sheet Close #1 'Sluit file nr. 1 Range("A1").Select Cells.Replace What:="1LS-Arial", Replacement:="1LS-OCR-B-10 BT", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom MkDir "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\" Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\00004583_001_KART_KM9_ V1.xml" For Output As #1 Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop End Sub Thanks for your response, Pieros |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
Code looks OK in terms of reading/write.
I can only say that either you data is not what you thing, or the .Replace is having unexpected effect . However, it looks like you can avoid using the Excel cells: Sub read_file() Dim strTemp As String Dim InFile As Long Dim OutFile As Long InFile = FreeFile OutFile = FreeFile(200) Open "C:\dat.txt" For Input As #InFile Open "C:\outdat.txt" For Output As #OutFile Do While Not EOF(InFile) 'Voer uit t/m de laatste regel van het file Line Input #InFile, strTemp 'Lees regel Print #OutFile, Replace(strTemp, "Find", "Replace") Loop 'Or you can replace the above loop with 'strTemp = Input(LOF(InFile), #InFile) 'strTemp = Replace(strTemp, "Find", "Replace") 'Print #OutFile, strTemp ' 'Or even 'Print #OutFile, Replace(Input(LOF(InFile), #InFile), "Find", "Replace") Close #InFile 'Sluit file nr. 1 Close #OutFile 'Sluit file nr. 1 End Sub NickHK "pieros" wrote in message oups.com... NickHK, - The Cells refer indeed to the same worksheet in both routines. - I am sure Cells(RowNdx, ColNdx) = "" does evaluate TRUE at line 100. The first empty cell is A112 and the RowNdx stops counting here. So that's working correctly. - I am resetting the RowNdx before the second loop. - I am sure all the data is read correctly in the first column. I send you my total code. (This code is only a test. If this works fine I will go on for all my files. It is at the same time good for me to practice so I later can do more with VBA). Here is my code: Option Explicit '*********************** Programma schrijft alleen de 1e 100 regels naar het aangegeven file - de laatste regel niet helemaal compleet ??????? ** Sub read_file() Dim strTemp As String Dim RowNdx As Integer Dim ColNdx As Integer Dim Line1 As String RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Approved\Cop00004500\00004583_001_KART_KM9_V 1.xml" For Input As #1 Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop Line1 = Mid(Cells(1, 1), 3, 21) Cells(1, 1).Value = Line1 'schrijf regel naar Excel-sheet Close #1 'Sluit file nr. 1 Range("A1").Select Cells.Replace What:="1LS-Arial", Replacement:="1LS-OCR-B-10 BT", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom MkDir "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\" Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\00004583_001_KART_KM9_ V1.xml" For Output As #1 Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop End Sub Thanks for your response, Pieros |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
Thanks NickHK, I will try your suggestions. I let you know!!
Pieros. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
NickHK, I tried the first part of your code and all I get is the following: 㼼浸敶獲潩㵮ㄢ〮㼢ാ㰊祄慮慍* *潲敪瑣*瑩敬橓扡潬湯㑟洰*扒* *敶獲潩㵮〢ㄮ㸢䐼湹浡牡扏敪 瑣*灹㵥䌢扏呪硥ാ㰊佃橢慂敳慄 *浥汰瑡敶獲潩㵮〢ㄮ㸢似橢慎 **灹㵥䌢瑓楲杮㸢硅䑰瑡㱥伯橢慎* ാ㰊慐慲*汥祡乳浡祴数千牴湩 䬾き猹慴摮慡摲慐慲*汥祡乳浡㹥 䤼癮牥 The chinese characters I get here are all squares in my "OutFile" So I do not get the right characters). Pieros. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
NickHK,
I also tried the second part with the "strTemp = Input(LOF(InFile), #InFile)" -line BUT here I get a Runtime error '62': Input past end of file. Help? Pieros pieros wrote: NickHK, I tried the first part of your code and all I get is the following: 㼼浸敶獲潩㵮ㄢ〮㼢ാ㰊祄慮慍* *潲敪瑣*瑩敬橓扡潬湯㑟洰*扒* *敶獲潩㵮〢ㄮ㸢䐼湹浡牡扏敪 瑣*灹㵥䌢扏呪硥ാ㰊佃橢慂敳慄 *浥汰瑡敶獲潩㵮〢ㄮ㸢似橢慎 **灹㵥䌢瑓楲杮㸢硅䑰瑡㱥伯橢慎* ാ㰊慐慲*汥祡乳浡祴数千牴湩 䬾き猹慴摮慡摲慐慲*汥祡乳浡㹥 䤼癮牥 The chinese characters I get here are all squares in my "OutFile" So I do not get the right characters). Pieros. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
I might be wrong, but in my guess, you seem to forget to close the output
file when exit sub, so data in a file buffer was'nt written into output file. Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop close #1 ' i think you need to close file here End Sub keizi "pieros" wrote in message oups.com... NickHK, - The Cells refer indeed to the same worksheet in both routines. - I am sure Cells(RowNdx, ColNdx) = "" does evaluate TRUE at line 100. The first empty cell is A112 and the RowNdx stops counting here. So that's working correctly. - I am resetting the RowNdx before the second loop. - I am sure all the data is read correctly in the first column. I send you my total code. (This code is only a test. If this works fine I will go on for all my files. It is at the same time good for me to practice so I later can do more with VBA). Here is my code: Option Explicit '*********************** Programma schrijft alleen de 1e 100 regels naar het aangegeven file - de laatste regel niet helemaal compleet ??????? ** Sub read_file() Dim strTemp As String Dim RowNdx As Integer Dim ColNdx As Integer Dim Line1 As String RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Approved\Cop00004500\00004583_001_KART_KM9_V 1.xml" For Input As #1 Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop Line1 = Mid(Cells(1, 1), 3, 21) Cells(1, 1).Value = Line1 'schrijf regel naar Excel-sheet Close #1 'Sluit file nr. 1 Range("A1").Select Cells.Replace What:="1LS-Arial", Replacement:="1LS-OCR-B-10 BT", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom MkDir "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\" Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\00004583_001_KART_KM9_ V1.xml" For Output As #1 Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop End Sub Thanks for your response, Pieros |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
The Print # command print not all rows from excel sheet to file
On Jan 12, 1:05 pm, "kounoike" wrote:
I might be wrong, but in my guess, you seem to forget to close the output file when exit sub, so data in a file buffer was'nt written into output file. Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop close #1 ' i think you need to close file here End Sub keizi "pieros" wrote in message oups.com... NickHK, - The Cells refer indeed to the same worksheet in both routines. - I am sure Cells(RowNdx, ColNdx) = "" does evaluate TRUE at line 100. The first empty cell is A112 and the RowNdx stops counting here. So that's working correctly. - I am resetting the RowNdx before the second loop. - I am sure all the data is read correctly in the first column. I send you my total code. (This code is only a test. If this works fine I will go on for all my files. It is at the same time good for me to practice so I later can do more with VBA). Here is my code: Option Explicit '*********************** Programma schrijft alleen de 1e 100 regels naar het aangegeven file - de laatste regel niet helemaal compleet ??????? ** Sub read_file() Dim strTemp As String Dim RowNdx As Integer Dim ColNdx As Integer Dim Line1 As String RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Approved\Cop00004500\00004583_001_KART_KM9_V 1.xml" For Input As #1 Do While Not EOF(1) 'Voer uit t/m de laatste regel van het file Line Input #1, strTemp 'Lees regel Cells(RowNdx, ColNdx).Value = strTemp 'schrijf regel naar Excel-sheet RowNdx = RowNdx + 1 'Volgende regel Loop Line1 = Mid(Cells(1, 1), 3, 21) Cells(1, 1).Value = Line1 'schrijf regel naar Excel-sheet Close #1 'Sluit file nr. 1 Range("A1").Select Cells.Replace What:="1LS-Arial", Replacement:="1LS-OCR-B-10 BT", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False RowNdx = 1 'Beginnen in 1e regel ColNdx = 1 'Beginnen in 1e kolom MkDir "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\" Close #1 'Sluit file nr. 1 Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 - mee bezig\Corrected\Cop00004500\00004583_001_KART_KM9_ V1.xml" For Output As #1 Do While Not Cells(RowNdx, ColNdx) = "" 'Voer uit t/m de laatste met tekst gevulde regel van het file Print #1, Cells(RowNdx, ColNdx).Value 'schrijf regel naar file RowNdx = RowNdx + 1 'Volgende regel Loop End Sub Thanks for your response, Pieros- Hide quoted text - - Show quoted text - You are right!! I forgot to close. All seems to work well but now I saw that the Euro character in my input file became a wrong unreadable character in my output file. I don't know what the reason is. It happens during the writing of the excel lines to the output file. ?????? Pieros. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| command line options/switches to print excel file | Excel Worksheet Functions | |||
| command line options/switches to print excel file | Excel Discussion (Misc queries) | |||
| how to print a sheet by command | Excel Discussion (Misc queries) | |||
| How to format the extension less file to print with Dos's Print Command | Excel Discussion (Misc queries) | |||
| Create command button to print multiple worksheets in a excel file | Excel Discussion (Misc queries) | |||
Hybrid Mode
