Macro to convert txt to xlsx is giving #N/A error on rows after certain point

Hi,

as this 'code' does not delete any row … As per forum rules you must edit your post and use the code tags !

So it seems there is a bad EOF character in the source text …

Thanks. You are right. "Delete" is the wrong word. After a certain row, it is giving me #N/A for all the rows after. I stepped through the macro and the error seems to happen here at this point.

With ActiveSheet.Range("A1").Resize(LineIndex, 1)
.Value = WorksheetFunction.Transpose(strLine)
.TextToColumns Other:=True, OtherChar:="|"

Edit: Let me try reviewing the EOF. I think I understand now what you are saying. This macro ought to be simple but it is confusing me none the less.

Attachment 729486

This is what happens, for visual reference.

When I delimit the file myself without the macro, there doesn't seem to be anything special at row 54102 that would trigger that #N/A error. Pictured attached.Attachment 729490

*Also added tags to VBA code.

Taking a better glance to your code, you must first check the value of the variable LineIndex just after the Close codeline
in order to be sure at least the entire text file is well read … If positive, you are certainly right for where the glitch may occur.

Transpose must be avoided - as maybe still limited to 65K rows - just declaring the array variable as 2 dimensions like Dim strLine(1 to Rows.Count, 0)

and to allocate a line use Line Input #1, strLine(LineIndex, 0) so ReDim Preserve is no more needed …

For further help if necessary, an attachment - follow the top page yellow banner - of the source text file will help to investigate your issue
with an explanation of the expected worksheet layout.

Here is a sample of the file. Just first few rows of data.

Would it be possible to modify the code using something else besides EOF to ensure entire file is well read to the end regardless of number of lines in original txt file?

Okay. I just reviewed the transpose as you mentioned and I now see the limit is 65K rows. So seems my entire code needs to be redesigned I guess. The only part that works well is the converting of the file from txt to xlsx and saving the txt files in new folder once converted to xlsx

When I use the macro recorder, I get this below. Is there someway to remove the EOF and transpose logic in my original code and replace with this and keep everything else as is that is working well? (modifying to accommodate looping through all files in folder) Or do you suggest I take a different approach?

Please Login or Register  to view this content.

Originally Posted by excelalejo

So seems my entire code needs to be redesigned I guess.

Not entirely just reading post #6 …

I see your post #6 however I am unsure how to make that modification in the code. I've tried testing different values in LineIndex but can't correct the error for some reason. Would you be able to help me make the modification?

As written in post #6 !

Please Login or Register  to view this content.

Thank you for your help and your time! I got it to work! Although I did have to make one minor change.

For this line: "Dim strLine(1 to Rows.Count, 0)" I changed "Dim" to "ReDim". Because, since this line is affecting array size, I was getting "constant expression required error" when using Dim!

Please Login or Register  to view this content.

Yes my bad for the Dim codeline 'cause I couldn't test ! The strLine variable can be String rather than Variant …
EOF is not an issue with 99.999% of text files.

Thanks for the rep' !