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

 

[gallen]

This syntax seems incorrect at first:

"C:desctop\ppp"

Also you need to show all your code

 

[gallen]

Your syntax is all over the place.

This code just worked for me:

Sub Import()
    Dim s As String, sLine As String
    Dim sFile As String
    Dim result() As String
    
    'Change to your file
    sFile = "C:\Test\ppp.txt"
    
    Open sFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, sLine
        s = s & sLine
    Loop
    result = Split(s, ",")
    Range("A1:A101") = WorksheetFunction.Transpose(result())
    
End Sub

 

[kramart]

Many thanks !!! it works almost perfectly ... the first value in the first cell has un expected and unwanted characters "ÿþ532424579" , also how to save this range as a cell reference "ppp" ? Cheers !

 

[gallen]

If those characters are there then you have something in your text file that is unwanted. Without looking at your file I couldn't help.

also how to save this range as a cell reference "ppp" ? Cheers !

Range("A1:A101").Name = "ppp"

 

[kramart]

You are absolutelly right. I used Word to save file and it added these two characters. It works perfect now! ...

Are these lines ok to add a new sheet and rename it ?

Sheets.Add After:=ActiveSheet
Sheets(1).Name = "ref1"
Sheets("ref1").Activate

Where shall i add it in your code to create a new sheet first and then run your code to import data ?

Many thanks in advance !!!

Cheers,

Art

 

[gallen]

Sub Import()
    Dim s As String, sLine As String
    Dim sFile As String
    Dim result() As String
    Dim ws As Worksheet
    
    'Change to your file
    sFile = "C:\Test\ppp.txt"
    
    Open sFile For Input As #1
    
    Do Until EOF(1)
        Line Input #1, sLine
        s = s & sLine
    Loop
    result = Split(s, ",")
    'Add new sheet
    Set ws = Worksheets.Add(After:=Sheets(Worksheets.Count))
    'Name sheet
    ws.Name = "ref1"
    'Paste results into new sheet
    ws.Range("A1:A101") = WorksheetFunction.Transpose(result())
    
End Sub

 

[kramart]

Works like a charm!!!

Many many thanks for your prompt replies !!! is it ok to approach you in the future with other vba issue ? hope it is !

All the best,

Artur

 

[kramart]

Hi,
How can I modify your code to add values "aaa, bbb, ccc, ddd, eee, fff" to the range c1:c6 on the same spreadsheet. Then name the cell range "soc". Next step I want to go back to the first spreadsheet despite its name ( I want to run this VBA in 45 workbooks and each of spreadsheets has different names .... like "all1" ,"all2", ... and so on. ) And insert names in "aa1", ab1", "ac3" ( let's say "one", "two", "three" ) three formulas (let's say SUM, COUNTIF, AVERAGE ) in the range "aa2: till the end of the column", "ab2: till the end of the column", "ac2:till the end of the column"
How can I do that ? Any advise would be much appreciated!

Thank you

Art

 

[gallen]

How can I modify your code to add values "aaa, bbb, ccc, ddd, eee, fff" to the range c1:c6 on the same spreadsheet. Then name the cell range "soc".

    ws.Range("C1:C6") = WorksheetFunction.Transpose(Array("aaa", "bbb", "ccc", "ddd", "eee", "fff"))
    ws.Range("C1:C6").Name = "soc"

Next step I want to go back to the first spreadsheet despite its name ( I want to run this VBA in 45 workbooks and each of spreadsheets has different names .... like "all1" ,"all2", ... and so on. ) And insert names in "aa1", ab1", "ac3" ( let's say "one", "two", "three" ) three formulas (let's say SUM, COUNTIF, AVERAGE ) in the range "aa2: till the end of the column", "ab2: till the end of the column", "ac2:till the end of the column"
How can I do that ? Any advise would be much appreciated!

Sorry, you completely lost me there. That's not straight forward.