Importing Data into excel longer then the columns

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Banker1

Active Member
Joined
Mar 10, 2005
Messages
463
Not sure I understand what your saying... you have a report that your importing that is larger than 256 columns wide?
 

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
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

Active Member
Joined
Nov 5, 2004
Messages
336
when you copy, have you tried using PASTE SPECIAL and check the TRANSPOSE box ?
 

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
Hi all, that didn't work :( I am getting 3 options when I do Paste Special:

1. HTML
2. Unicode Text
3. Text

Thoughts? :unsure:
 

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
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;
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,750
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top