0

I am trying to write a code that will allow me to import an external text file and format it by column.

I have tried a few different methods and I think delimiting is the best method for what I want to do.

Sub Import_TxtFile()
Dim TXT As Range


Open "C:\Users\hpeshek\Desktop\Excel Testing\Test 3.txt" For Input As #1

'Run the procedure while it is not at the end of the file
Do While Not EOF(1) 'Loop until End of File
Line Input #1, TXT  'Read line into variable

'I think if i can get the following to work then the import will be successful

TXT.TextToColumns _
Destination:=Range("A1:"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
OtherChar:=False


Loop
Close #1 'Close file



End Sub

I think my issue is that I can't format the TXT as a range for TextToColumns function. Does anyone know what it should be designated as?

ahamhpesh
  • 1
  • 2

1 Answers1

0

You have a comma and an underline at the end of your "OtherChar" option, which probably shouldn't be there as it's the end of the line. You also mis-spell "False" as "Flase" on that same line, except that the docs say that OtherChar is a variant, not a boolean. Could that be part of the problem?

A lot of those parameters are optional and have the same defaults as the values you have set, so you might be better leaving those out.

droopsnoot
  • 931
  • 1
  • 7
  • 11
  • Yes, thank you! That got rid of the syntax error. However, now it is highlight "TXT" in the TXT.TextToColumns line and giving an "Invalid Qualifier" error. Do you know why this is? – ahamhpesh Jun 18 '19 at 18:20
  • The doc suggests that "TextToColumns" requires a Range object rather than an input string. I have little or no VBA experience, so I can't help further I'm afraid. – droopsnoot Jun 18 '19 at 18:25
  • Okay, thank you for your help! I'll try switching it to a range. – ahamhpesh Jun 18 '19 at 18:41