The Print # command print not all rows from excel sheet to file

[pieros]

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.

 

[NickHK]

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]

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_V1.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

 

[NickHK]

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]

Thanks NickHK, I will try your suggestions. I let you know!!

Pieros.

 

[pieros]

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.

 

[pieros]

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

 

[NickHK]

You have Unicode text files ?

Nick

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]

Good morning NickHK (It now is here about 08:40 am),

My text files are good readable characters. Here is an example:

<?xml version="1.0"?>
<DynaMark-Project title="Sjabloon_40mm_Rb.xml" version="0.1">
<Dynamark-Object type="CObjText">
<CObjBaseData template-version="0.1">
<ObjName type="CString">ExpDate</ObjName>
<ParamDelaysName type="CString">KM09standaard</ParamDelaysName>
<Invers type="bool" value="false"/>
<Mark type="bool" value="true"/>
<Offset type="CPoint" value="-1908788 -725252"/>
<ScaleX type="double" value="1.8"/>
<ScaleY type="double" value="2.2"/>
<RadiantX type="double" value="-1.5708"/>
<RadiantY type="double" value="-1.5708"/>
<MirrorX type="bool" value="true"/>
<MirrorY type="bool" value="true"/>
<LaserNr type="int" value="0"/>
<MinVectorLength type="double" value="0"/>
<XCenterMode type="int" value="0"/>
<YCenterMode type="int" value="0"/></CObjBaseData>
<CObjText>
<Text type="CString">2222222</Text>
<FontName type="CString">1LS-Arial</FontName>

And than about 100 - 120 lines for each file.
So there seems nothing wrong with the text.

My goal is to replace the text 1LS-Arial or 1LS-Arial-U in 1LS-OCR-B-10
BT in each file but in some files the text 1LS-OCR-B-10 BT is already
present so needs no modification.

Pieros.

 

[NickHK]

I can't repeat your error(s) using this code your XML file.
It replaces fine, no Chinese or errors.

Dim InFile As Long
Dim OutFile As Long

InFile = FreeFile
Open "C:\Replace.txt" For Input As #InFile

OutFile = FreeFile
Open "C:\outdat.txt" For Output As #OutFile

Print #OutFile, Replace(Input(LOF(InFile), #InFile), "1LS-Arial",
"1LS-OCR-B-10 BT")

Close #InFile
Close #OutFile

As for the files, whilst they may be text, they can still be saved in a
Unicode format. Open in NotePad, do a SaveAs and check the Encoding setting.
However, I get different errors to those you mentioned if the file is
Unicode.

NickHK

 

[pieros]

I tried your last code to see what happens but I get a Compile Error:
"Wrong number of arguments or invalid property assignment" at the
Replace line. My Excel version is Microsoft Excel 2002. Maybe that's a
problem?

Pieros

 

[NickHK]

This line :
Print #OutFile, Replace(Input(LOF(InFile), #InFile), "1LS-Arial",
"1LS-OCR-B-10 BT")

needs to be all one line. The news reader wrapped it.

NickHK

 

[pieros]

NickHK I'm sorry but when I use this code

Option Explicit

'*********************** Programma schrijft alleen de 1e 100 regels
naar het aangegeven file - de laatste regel niet helemaal compleet
??????? **

Sub read_file()

Dim InFile As Long
Dim OutFile As Long

InFile = FreeFile
Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 -
aangepast volgens
forum\Approved\Cop00004500\00004583_001_KART_KM9_V1.xml" For Input As
#InFile

OutFile = FreeFile
Open "U:\Programs - Documents\MSExcel\Test zoek en vervang 4 -
aangepast volgens
forum\Corrected\Cop00004500\00004583_001_KART_KM9_V1.xml" For Output As
#OutFile

Print #OutFile, Replace(Input(LOF(InFile), #InFile), "1LS-Arial",
"1LS-OCR-B-10 BT")

Close #InFile
Close #OutFile

End Sub

I get the Run-time Error 62 again. (Input past end of file)
My text replacement wish is not a simple one I believe.

Pieros

 

[NickHK]

If you get that error, then you have text files with Unicode encoding. As
such, this code will not work.

You can adapt this :
http://support.microsoft.com/kb/193540

Or maybe use the FSO:
http://www.aivosto.com/visdev/fso.html

However, as your file are XML, you can open them directly in XL2002.
However, with your file, I get an error, as the first line "<?xml
version="1.0"?>" is invalid.
Not sure how Excel would handle the encoding either.

NickHK

 

[pieros]

Anyway, thanks NickHK for your help sofar. I will try what you suggest
and hope it will work.

Thanks again,
Pieros.

 

[NickHK]

I don't use XML much, but it seems that your files are encoded but the XML
declaration does not include the encoding used. Hence Excel fails because it
assumes UTF-8, which is wrong.
Maybe one of these will help
http://msdn.microsoft.com/msdnmag/issues/01/05/xml/
http://www.w3.org/TR/2004/REC-xml-20040204/#sec-guessing

VBA can still work with these files, but you have to allow for the
differences between these and normal ANSI files, which was assumed for the
code before.

You can send me a sample of the XML file if you want and I can see what you
have.
(e-mail address removed)

But I recommend you read up on encoding, XML and how Excel deals with both
of those, as I do not know much : ) .

NickHK

 

[kounoike]

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]

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.


close #1 ' i think you need to close file here




keizi































- 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.