How do I read named ranges from a text file into excel using VBA?

[sarithgada86]

Hey All,

I am trying to write some code to read values back into a named range from a .txt document. The code I used to write it out was:

Sub RangeToText()
Set fs = CreateObject("Scripting.FileSystemObject")
Set x = fs.CreateTextFile(Range("filepath") & Range("studentname") & " " & Range("Basegroup") & ".txt")
For a = 1 To Range("rangeformacro").Rows.Count 'a range of named ranges to loop through
b = Range("rangeformacro").Rows(a) 'find the named range I want to work through

x.writeline b 'state the named range

For c = 1 To Range(b).Rows.Count 'loop through to write out data in that range

x.writeline (Range(b).Rows(c))

Next c
Next a
End Sub

The text file this produces looks like this:

StudentName (Named range is studentname)
Joe Bloggs (Data in this named range etc...)
BaseGroup
7H
College
Holmes College

Unfortunately, I do not know where to start to write the code to read it back in!

Help please?!

Thanks!

Sarith

 

[John_w]

You could use FileSystemObject methods OpenTextFile and ReadLine, or VBA Open and Line Input statements. In both cases you'll need to check for the end of the file before reading the next line.

 

[sarithgada86]

Thanks for the reply John. But what code do I need to write? The coding is my downfall, never done this type of programming before!

Thanks,

Sarith