Importing Data into excel longer then the columns

[ineedadedt]

Hello all,

I have a report that I need to import into excel that when I try to copy and paste runs longer then the columns. I can only get about 1/3 of the report. The problem is that I need it all on one page and there are no options to export the data from the program. I have tried to save it as a text file but when importing it from a .txt file, it works for a few columns but then gets mashed together. Any idea of how to import the information vertically instead of horizontally?

Any thoughts are greatly appreciated!

Eric

 

[Banker1]

Not sure I understand what your saying... you have a report that your importing that is larger than 256 columns wide?

 

[ineedadedt]

correct...the report runs horizontally, so when trying to copy and paste it on excel it runs out of room. I need a way to paste it vertically.

 

[Fat Cat]

when you copy, have you tried using PASTE SPECIAL and check the TRANSPOSE box ?

 

[ineedadedt]

Hi all, that didn't work I am getting 3 options when I do Paste Special:

1. HTML
2. Unicode Text
3. Text

Thoughts?

 

[Fat Cat]

What sort of data is in the file you want to import ?
Is it all text or is it text and numbers ?
What sperates one data item from the next ? is it a comma or a space or tab etc.

Since your only Paste Special options available appear to be text based, you could instead use a VBA macro to do the transposing for you.

This can either be from a CommandButton placed on your spreadsheet or a Macro inserted into a Module that you then run from the TOOLS - MACRO menu.

example;

Private Sub CommandButton1_Click()

Dim myText As String
Dim myData As Variant
Dim rowCounter As Long
Dim colCounter As Long

rowCounter = 1
colCounter = 1

Open "My old text file.txt" For Input As #1             'open your text file
Do
    Line Input #1, myText                               'read first line in as text
    myData = Split(myText, ",")                         'split up into data based on "," separator between data
    For i = 0 To UBound(myData)                         'work out how many data items you now have
        Cells(rowCounter + i, colCounter) = myData(i)   'write each data item to separate rows on your sheet
    Next i
    rowCounter = 1                                      'set the row counter back to 1 (the top)
    colCounter = colCounter + 1                         'set the column counter to 1 more so we now go down the next column
Loop Until EOF(1)                                       'keep going till the end of the file is reahced
Close #1                                                'close the file

End Sub

If you want help creating the Macro, just holler