Forum Home
Press F1
 
Thread ID: 27387 2002-11-21 07:38:00 Excel and filenames again! Mike (15) Press F1
Post ID Timestamp Content User
100223 2002-11-21 07:38:00 This time I've got 5500+ files listed all in one column, and to print it'll take 195 pages, so I was wanting to cut and paste all the files so they fit accross the page in columns as well (fit about 9 columns per page, but not sure how many cells down the page). All up I think it was going to come to around 20 pages. But it's going to take me all day to cut and paste all the new page-long columns - is there an easier way? :D The excel sheet isn't even for my final product - it's just so I can print it and check off filenames on something else (not near a computer, so I can't just check on the PC everytime).

Does all that make sense?

Instead of having (for example):
file1
file2
file3
file4
file5
file6
file7
file8
file9
file10
file11
file12
I rather want:
file1 file4 file7 file10
file2 file5 file8 file11
file3 file6 file9 file12
Mike. 		Mike (15)
100224 2002-11-21 08:24:00 If you have Access, import the list from excel (File/Get External Data). Create a report from the table generated from the import. Goto 'File/Page Setup/Columns' and adjust the number of columns you want in the report. wuppo (41)
100225 2002-11-21 12:19:00 Make the boss pay up for a work laptop (you need a way to get work home anyway). hmmm a 2GHz+ P4 with a GF4 2go should do the trick ]:) -=JM=- (16)
100226 2002-11-21 20:11:00 No Access on this machine :(

Mike. 		Mike (15)
100227 2002-11-21 20:12:00 I work for the Government, JM... that's not going to happen :)

Mike. 		Mike (15)
100228 2002-11-21 20:28:00 How about pasting the list into Word and formatting as multicolumn pages? wuppo (41)
100229 2002-11-21 21:48:00 >>> How about pasting the list into Word and formatting as multicolumn pages?

That might just work :) thanks Wuppo!

Mike. 		Mike (15)
100230 2002-11-21 22:09:00 The following macro will do this.

Copy your file list to cell A1 in new worksheet.

Edit the number of lines per page from 79 to whatever you need.

Sub file_split()
Dim n, m, pl As Integer
Dim rng As Range
'pl = lines per page
pl = 79
m = Round(Range("a1", Range("a1").End(xlDown)).Cells.Count / pl)
Range("a1").Select
n = -1
Do
n = n + 1
ActiveCell.Offset(pl, 0).Select
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
rng.Select
rng.Cut Range("a1").Offset(0, n + 1)
Range("a1").Offset(0, n + 1).Select
Loop Until n = m - 1
End Sub

HTH 		Russell D (18)
100231 2002-11-21 23:44:00 Or you might try this macro:
List your column of filenames starting in A1. Enter the number of rows you want the new columns to take up in G1 and go to it.


Dim cof As Integer, i As Integer, j As Integer, x As Integer
Dim rown As Integer, coln As Integer
Range("G1") = 3
Range("D1") = "Filenames wanted per row"
rown = Range("G1") 'numbers of file names you want in each row

'cof is count of file names in column 1 starting from row 2 and finishing when get to empty cell
cof = 0
Do While IsEmpty(Cells(cof + 1, 1)) = False
cof = cof + 1
Loop
Cells(1, 2) = cof & " filenames)"

'calculate numbers of colums needed
coln = Int(cof / rown) + 1

'putting nrow file names in each column
For j = 1 To coln
For x = (j - 1) * rown + 1 To j * rown
Cells(x - (j - 1) * rown + 2, j + 2) = Cells(x, 1)
Next x
Next j

End Sub 		rugila (214)
100232 2002-11-22 00:55:00 Two macros have been suggested to do the job, both seem to work OK.
Just for interest I tried both of them to list a single column of 25000 entries into 250 columns of 100 entries in each. With my machine one macro took 2 minutes, the other took 5 seconds to do the same job.
What does this show? Nothing at all really, but for interest nevertheless.
Mapping 64000 entries columns with 300 entries in each took 16 seconds with one macro, with the other I had time to have my morning coffee. 		rugila (214)
1 2 3 4 5