FreeFile to read a (multicolumn) text file

CatWithaWhiteHat

New Member
Joined
Apr 10, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I have been able to put together the below code.
It reads the cell contents into a text file.

But I cant work out how to generate the code necessary to read a text file back into an excel worksheet.
Any help in generating the necessary code is gratefully appreciated




VBA Code:
Sub test3()
Dim ifile1 As Integer
Dim irow As Integer

Dim iName As String
Dim icolour As String
Dim inum As String
'free file number
ifile1 = FreeFile
'location to write to
Open "D:\XXXX\Documents\XXXX Test Freefile3.txt" For Output As #ifile1
'line to start at
irow = 2
'read through the cells
Do
With Sheets("Sheet1")
iName = .Cells(irow, 1).Value
icolour = .Cells(irow, 2).Value
inum = .Cells(irow, 3).Value
End With
'action to do
Write #ifile1, iName, icolour, inum
'step each time to a new line
irow = irow + 1
'loop to do till there is no more data in the cells
Loop Until IsEmpty(Sheets("Sheet1").Cells(irow, 1).Value)
'close the open file
Close #ifile1
End Sub
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
When writing to the file you should add a delimiter, e.g. comma, between the columns, then when you read back from the file you can split each line by that delimiter using the Split function and then populate the cells with the resultant array.
 

CatWithaWhiteHat

New Member
Joined
Apr 10, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
so is there a read equiverlent to the write #ifile1 that I can use to read the text file?
I'm guessing I have to use EOF and loop to go through the text file. But I have no idea how to input in the data

VBA Code:
Do Until EOF

Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top