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
100233	2002-11-22 03:14:00	What might work is to Export as comma delimited file, then import it as comma delimited file to a data base with the appropriate number of text fields per record. You might be able to specify a spreadsheet to do it ?thatt way? I don't know.:D Rude and crude however you do it ... I'd write a little programme in Pascal to do it, myself.	Graham L (2)
100234	2002-11-23 21:31:00	The file was originally a CSV file from a dir /b *.tif > files.csv - that's how I got all the file names in there to start with :) But no database program on my PC. I tried Wuppo's method of just exporting into Word and setting it up with a number of columns per page - it worked brilliantly, and I had it all done within around 5-10 seconds (had to wait for Word to open ;)) Thanks people :) Mike.	Mike (15)
100235	2003-12-16 02:10:00	Russel D or Rugila or anybody else who might be able to edit one of the macros, or create a new one ;), I'm back again with a similar problem, but this time my doing it in Word doesn't work (too many entries!) I tried both your macros, and they both work, to a point :) They stop when they hit column 256 (IV or something I think). Is it possible to set it to automatically skip back to column A and then continue? Or perhaps go page by page but down (instead of across) the screen? I need the numbers to list down in columns, but one page at a time. Does that make sense??? Cheers, Mike.	Mike (15)
100236	2003-12-16 03:48:00	G'Day this took about 3 secs for 30,000 on my PC. Take a not of how many rows you have and take a guess at the columns (say 9) then run the macro. It will ask you for # rows & cols then create a new sheet called zigzag and place the value in there. Im presuming they are all in column A starting from A1. Repeat until your happy with width/height to get the max per sheet. All depends on font size etc so Ive made it flexible for you to play around. I havent bothered to put any error checking so if ZigZag sheet already exists the code will bomb so just kill the sheet if you want to run it again with different parameters. Sub ZigZag() 'By Parry 16/12/03 Dim Rng As Range, MyArray() As String, Arr1 Dim NumCols As Integer, NumRows As Long, NumFiles As Long Dim R As Long, C As Integer, J As Long, Result As Range 'Set range holding file names Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row) 'Find # rows & columns wide for the print NumCols = Application.InputBox(prompt:="Enter # columns", _ Title:="Width of Print Job", Type:=3) NumRows = Application.InputBox(prompt:="Enter # rows", _ Title:="Height of Print Job", Type:=3) 'Determine array dimensions NumFiles = Rng.Cells.Count If NumFiles Mod NumCols = 0 Then Arr1 = NumFiles / NumCols Else Arr1 = NumFiles / NumCols + 1 End If ReDim MyArray(1 To Arr1, 1 To NumCols) For R = 1 To Arr1 For C = 1 To NumCols J = J + 1 MyArray(R, C) = Cells(J, 1).Value Next C Next R 'Add new Sheet to store the result Sheets.Add Sheets(1).Name = "ZigZag" Set Result = Sheets(1).Range(Cells(1, 1), Cells(Arr1, NumCols)) Result = MyArray End Sub hth	parry (27)
100237	2003-12-16 03:55:00	Mike, It looks like you may need to split the list of files between a couple of worsheets before running the print macro to avoid the 256 column limit. For example, if there are 9 columns and 79 rows per page then the maximum number of whole pages per worksheet is 256/9 = 28. This equates to 9x28x79 = 19908 files per worksheet. If you cut all rows below row 19908 and paste them to another worksheet at cell A1 - if there are files in rows below 19908 then cut these and paste to a further sheet. Repeat until there are no more entries below 19908. Then run my previous macro on each sheet in turn. Substitute your actual rows per page and columns per page in the above equations to determine the row number to cut and paste from - then edit the number 79 in the macro to suit the actual rows per page. Hope this makes sense.	Russell D (18)
100238	2003-12-16 04:36:00	Thanks Parry, I ran your macro, however after a couple of seconds I get an error message that reads "400" :) I get a new worksheet called ZigZage, but it's empty. Mike.	Mike (15)
100239	2003-12-16 04:41:00	> worksheet called ZigZage, but it's empty. ZigZag :) Mike.	Mike (15)
100240	2003-12-16 04:51:00	> Thanks Parry, > > I ran your macro, however after a couple of seconds I > get an error message that reads "400" :) I get a new > worksheet called ZigZage, but it's empty. > > Mike. Debugging it, it gives me an error when I hit the second to last line "Result = MyArray (or it could possibly be stopping on the line before that?): Run-time error '1004': Application-defined or object-defined error Mike.	Mike (15)
100241	2003-12-16 05:08:00	Weird, as it worked without a hitch for me. Is the sheet that has the file names the active sheet when you run the code? It needs to be. Plus do you have file names in cells A1 downwards? Im not sure what err 400 is - whats the description? Heres an update to kill the ZigZag sheet if it exists & some rudimentary checking. Sub ZigZag() 'By Parry 16/12/03 Dim Rng As Range, MyArray() As String, Arr1 Dim NumCols As Integer, NumRows As Long, NumFiles As Long Dim R As Long, C As Integer, J As Long, Result As Range Dim Sh As Worksheet, Exist As Boolean On Error GoTo ErrHand 'Check range If Range("A1").Value = "" Then MsgBox "You must have a value in cell A1!" Exit Sub End If 'Set range holding file names Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row) 'Find # rows & columns wide for the print NumCols = Application.InputBox(prompt:="Enter # columns", _ Title:="Width of Print Job", Type:=3) NumRows = Application.InputBox(prompt:="Enter # rows", _ Title:="Height of Print Job", Type:=3) 'Determine array dimensions NumFiles = Rng.Cells.Count If NumFiles Mod NumCols = 0 Then Arr1 = NumFiles / NumCols Else Arr1 = NumFiles / NumCols + 1 End If ReDim MyArray(1 To Arr1, 1 To NumCols) For R = 1 To Arr1 For C = 1 To NumCols J = J + 1 MyArray(R, C) = Cells(J, 1).Value Next C Next R 'Add new Sheet to store the result On Error Resume Next Set Sh = Worksheets("ZigZag") If Error Then Application.DisplayAlerts = False Sheets("ZigZag").Delete Application.DisplayAlerts = True Err.Clear End If Sheets.Add Sheets(1).Name = "ZigZag" Set Result = Sheets("ZigZag").Range(Cells(1, 1), Cells(Arr1, NumCols)) Result = MyArray Exit Sub ErrHand: MsgBox "Error # " & Err.Number & vbLf & Err.Description Exit Sub End Sub	parry (27)
100242	2003-12-16 05:14:00	Debugging it, it gives me an error when I hit the second to last line "Result = MyArray (or it could possibly be stopping on the line before that?): Run-time error '1004': Application-defined or object-defined error Mike. Ok, that sounds like theres nothing in the array or the dimensions of the range and the array dont match. This would probably happen if the active sheet is not the one with the file names or theres nothing in Column A. When you enter the number of rows this should match the number of filenames you have. Let me know how you get on.	parry (27)
1 2 3 4 5