Transpose values from txt to spreadsheet

Case:

Public Sub Example1()

'dialog result
Dim intDialogResult As Integer

'path selected by user
Dim strPath As String

'single line of data from the text file
Dim strLine As String

'string seperated by the delmiter
Dim arrString() As String

'curren row in excel sheet
Dim i As Integer

Dim FileName As String

'disallow user from selecting multiple files
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False

'remove previous filters
Call Application.FileDialog(msoFileDialogOpen).Filters.Clear

'display the open file dialog
intDialogResult = Application.FileDialog(msoFileDialogOpen).Show

'if the user selected a file
If intDialogResult <> 0 Then

'path selected by the user
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)

'close the file index #1 if its already opened
Close #1

'open the file for reading
Open strPath For Input As #1


i = 1
'loop while the end of file has not been reached

While EOF(1) = False
    'read one line of data from the text file

    Line Input #1, strLine

    'split string based on delimiter
    arrString = Split(strLine, " ")

    'I only need the 6th column values
    ActiveCell(i, 2) = arrString(6)
    i = i + 1

Wend
End If
Close #1

ActiveCell.Offset(0, 1).Activate

End Sub

Basically what it does is it takes values from 6th column from a .txt file and puts them next to each other each time the macro runs.

Problem 1: I want to store them horizontally (up to 200 values in each column from .txt file)

Problem 2: I want to set the first cell as the file name

Answers


You can do this with shortcut keys. If you want to see the code, turn on Record Macro:

On sheet 1:

select any cell within your array Ctrl+a <-select all Ctrl+c <- copy

Move to sheet 2:

select cell A1 Ctrl+Alt+v <- Paste Special e <- Transpose Enter <- Paste


To fill data across columns instead of down the rows, you can change

ActiveCell(i, 2) = arrString(6)

to

ActiveCell(1, i) = arrString(6).

To extract the filename from StrPath use

FileName = StrReverse(Left(StrReverse(strPath), InStr(1, StrReverse(strPath), Application.PathSeparator) - 1))

(you declared FileName as String but didn't use it)

To place FileName on the first cell just add ActiveCell = FileName at the line just before entering the While loop, and set i to start from 2 (i.e. i = 2)

For the part concerned, the whole changes would look like (comments removed):

FileName = StrReverse(Left(StrReverse(strPath), InStr(1, StrReverse(strPath), Application.PathSeparator) - 1))
ActiveCell = FileName

i = 2
While EOF(1) = False
    Line Input #1, strLine
    arrString = Split(strLine, " ")
    ActiveCell(1, i) = arrString(6)
    i = i + 1
Wend

And finally, if my wild guess is right, to activate the first cell on the next row, use

ActiveCell.Offset(1, 0).Activate


Need Your Help

C#: Iterating through a huge datatable

c# loops datatable

Iterating through a datatable that contains about 40 000 records using for-loop takes almost 4 minutes. Inside the loop I'm just reading the value of a specific column of each row and concatinating...

How to add roles to rails app from console? Cancan, Devise

ruby-on-rails authentication devise cancan

I can create user already, but how add roles dynamically? from console or with admin rules? Devise, CanCan. I am new at Rails, but if you will propose me some ideas or materials, i will be happy.