Removing Quotation from text. (Using EOF)

[tjsdndnjs]

Hey guys. I am trying to write .txt into excel using VBA.

This is something I got from the internet. I've attached the text file.

I am trying to select the first couple of lines which has quotation around them. What can I do to select the lines with quotation and remove them. At the same time, leaving the numeric data lines written on excel.

Thank you.

Public Sub textToexcel()
Dim FilePath As String
Dim strLine As String
Dim i As Integer

Dim lines As Collection
Set lines = New Collection

FilePath = "C:\Users\Deepsheit\Desktop\School\lab 5\singleSpecimen.txt"
Open FilePath For Input As #1

If Err <> 0 Then
MsgBox "Not found: " & Filename, vbCritical, "ERROR-GDS"
Exit Sub
End If
i = 1
While EOF(1) = False
Line Input #1, strLine
Cells(i, 1) = strLine
i = i + 1

Wend



End Sub

[shg]

How about ...

Please Login or Register  to view this content.

[tjsdndnjs]

Thanks for the reply shg. Maybe I was not clear with my question.

As I run the above code in the macro, it writes the info. from text to excel. However, they show the quotation for the first 5 lines which are

""
"Test Method" "TENSILE MASTER"
"Sample I. D." "OSB"
"Specimen Number "1"

"EXTENSION (In)"" LOAD (Lb)"

I think I am suppose to use replace() function and extractelement() function... Is there a way I could select only those lines with quotation and remove them?

leaving the data lines the way it is...

Thanks.

[shg]

What do you see when you run the code I posted that isn't what you want? I don't see any quotation marks.

[tjsdndnjs]

Oh shoot, my bad there is supposed to be quotation in the .txt file that i attached.

This new attached .txt contains the file with quotations around the first couple of lines.

Thanks.

[shg]

Please Login or Register  to view this content.

[jindon]

Do you mean like this?

Please Login or Register  to view this content.

[tjsdndnjs]

YES! IT WORKED.

Thank you so much shg and jindon i really appreciate your help!

SHG, I have another question regarding "Const sFile", "If Len(Dir(sFile)) Then" and, " Cells.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart"

I get how "Len" works since it returns the length of the string and "Dir" is calling the specific filename...

However, what does the "Const" do?

Also, for "" Cells.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart""

This function basically replaces Chr(34) (quotation) with empty spaces if I am correct?

Thanks.

[shg]

1. Const declares a value as a constant, a value that is defined at compile time and cannot change at run time. The expression may use nothing but constants, operators, and arithmetic.

2. It deletes quotation marks, yes.