VBA insert values into a range of cells

[kramart]

Hi,

I have a string of 101 values separated by comas and saved as "ppp.txt" document. I need to create a new spreadsheet called "ref1" then insert the values to each cell of the 1st column A1:A101 from this document and then name the range A1:A101 as "ppp".

Sheets.Add After:=ActiveSheet
Sheets(1).Name = "ref1"
Sheets("ref1").Activate
Range("A1:A101").Select
Range("A1:A101").Value = File.ReadAllText("C:desctop\ppp")
ActiveWorkbook.Names.Add Name:="ppp", RefersToR1C1:="=Sheet2!R1C1:R101C1"

The code doesn't work ... any help will be much appreciated !!!

Thanks,

Art

 

[kramart]

First off, for my own curiosity, I would like to know if my guess in Message #18 was correct or not.

I don't see any commas ... not even after the last item ... I had a column with numbers then using formula B2= A1&"," in the column 2 I created numbers with commas and then copied and pasted into Notpad and saved as .txt file. So Gallen's code worked fine for me.

Just so you know, each line in a text file created on a PC (not a Mac or Linux computer) has a Line Feed followed by a Carriage Return at the end of that line's text, so you actually have a comma followed by Line Feed followed by a Carriage Return after each of those word, not just a comma. Here is the code that will read your list into memory, split it than then distribute each file line to its own cell in Column A starting in cell A1...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub GetCommaNewlineDelimitedTextFile()
  Dim FileNum As Long, TotalFile As String, FileLines() As String
  FileNum = FreeFile
  Open "c:\temp\temp.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  FileLines = Split(TotalFile, "," & vbNewLine)
  Range("A1").Resize(UBound(FileLines) + 1) = Application.Transpose(FileLines)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks for your code Rick!

I modified Gallen's code and was able to achieve what I wanted. I did exactly the same steps to create the .txt file but this time with text instead of numbers.

Dim h As String, hLine As String
Dim hFile As String
Dim hresult() As String

'Change to your file
hFile = "C:\Users\Desktop\head.txt"

Open hFile For Input As #2

Do Until EOF(2)
Line Input #2, hLine
h = h & hLine
Loop

hresult = Split(h, ",")

'insert row
Rows(1).Insert

'Paste results into sheet row
Range("A1:AJ1") = hresult()

I am completely new to VBA so apologies for any confusing questions.

Many thanks to both of you!!!

 

[kramart]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub GetCommaNewlineDelimitedTextFile()
  Dim FileNum As Long, TotalFile As String, FileLines() As String
  FileNum = FreeFile
  Open "c:\temp\temp.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  FileLines = Split(TotalFile, "," & vbNewLine)
  Range("A1").Resize(UBound(FileLines) + 1) = Application.Transpose(FileLines)
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

the code inserts values into column A insted of row A ...