Replies

Catharine,

Here is another way to do it. The code is even worse than “rough”. But it did work on a few tests I ran…

‘—————————————————————————————–
‘The following code allows the importation of delimited text files,
‘that exceed 256 columns, directly into a Excel spreadsheet.

‘The code was written using the MSKB article # 120596
‘”XL: Importing Text Files Larger Than 16384 Rows” as a base.
‘It was modified by using a Byte array to check the number of
‘delimiters in each file string and to split the string in half if
‘there are more than 255 delimiters. Each half is then put in adjacent
‘rows, one below the other. Each split row is noted with the word
‘”continued” at the beginning of the adjacent row.
‘If the string exceeds 512 chunks (columns), then the code will have to be modified.
‘The Excel “Text to Columns”‘utility can be used to parse all rows.

‘Code modified by Jim Cone on May 10, 2003.
Sub LargeFileImport()
Dim ResultStr As String
Dim ResultStr2 As String
Dim FileName As Variant
Dim FileNum As Integer
Dim Counter As Long
Dim i As Long
Dim N As Long
Dim TooLong As Boolean
Dim ChunkCount() As Byte

‘Ask User for File’s Name
FileName = Application.GetOpenFilename
‘Check for no entry
If Len(FileName) = 0 Or FileName = False Then End
‘Get Next Available File Handle Number
FileNum = FreeFile()
‘Open Text File For Input
Open FileName For Input As #FileNum
‘Turn Screen Updating Off
Application.ScreenUpdating = False
Worksheets.Add before:=Sheets(1), Count:=1

‘Set The Counter to 1
Counter = 1
‘Loop Until the End Of File Is Reached
Do While Seek(FileNum) 255 Then
TooLong = True
Exit For
End If
End If
Next ‘i
N = 0
‘If more than 256 chunks (columns)
If TooLong Then
ResultStr2 = Right$(ResultStr, (Len(ResultStr) 2) – 1)
ResultStr = Left$(ResultStr, Len(ResultStr) 2)
If Left(ResultStr, 1) = “=” Then
ActiveCell.Value = “‘” & ResultStr
Else
ActiveCell.Value = ResultStr
End If
ActiveCell(2, 1).Value = “CONTINUED ” & ResultStr2
ActiveCell(2, 1).Select
TooLong = False
Else
‘Store Variable Data Into Active Cell
If Left(ResultStr, 1) = “=” Then
ActiveCell.Value = “‘” & ResultStr
Else
ActiveCell.Value = ResultStr
End If
End If

If ActiveCell.Row = Rows.Count Then
‘If On The Last Row Then Add A New Sheet
Worksheets.Add before:=Sheets(1), Count:=1
Else
‘If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
‘Start Again At Top Of ‘Do While’ Statement
Loop
‘Close The Open Text File
Close
Application.StatusBar = False
End Sub
‘——————————————————–

Regards,

Jim Cone
San Francisco, CA
jim.coneZZZ@rcn.comXXX

Dean,

Thank you for responding.

Yes, I would like to track down the source of the error you reported, but that can be very difficult via email.
Could you determine if there are any protected sheets in the problem workbook and, if so, unprotect them and try again.
Also, I would like to know the Excel version and what service packs have been applied.
Any further comments you might have would be appreciated.

The program does take protected sheets into account, but maybe something happened I didn’t consider.
For what its worth, the program has been run on NT4.0 and Windows 95 & XP with both XL97 and XL2002.

Finally, were you able to accomplish the original task of removing unwanted styles?

Regards,

Jim Cone

Dean,

Here you go…

Attached is “Formats and Styles” Excel add-in.

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Dean,

I’ve written an Excel add-in (“Formats & Styles”) that will list all styles in a workbook or delete all unused styles in the workbook.
It has the same options for custom number formats.
An additional option is available to batch delete selected styles.
A new menu item is added to the format menu that provides access to the program.
I’ve attached the Word.doc instruction page (31kb-zipped).
If you or anybody else is interested, post back and I will attach the program (98kb zipped) in a separate post. (100 kb limit).

Regards,

Jim Cone
San Francisco, Ca
jim.coneXXX@rcn.comXXX

Hans,

You are right and you know the answer, but for others…

Replace…
For N = 97 To 122 ‘removes lower case a to z

with…
For N = 48 to 57’ removes 0 to 9

Also, adding this additional line, before the loop removes all spaces…
ColRng.Value = Application.Substitute(ColRng, Chr$(32, vbNullString)

Regards,
Jim Cone

Smbs,

The following code works If all the extras characters are lower case and consist only of a to z.
It can be modified to remove other characters as necessary…

Sub RemoveAlphasFromRange()
Dim ColRng As Range
Dim N As Long

Set ColRng = Range(“C1:C1000”) ‘ Do not us an entire column
For N = 97 To 122 ‘ Character codes for lower case a to z
ColRng.Value = Application.Substitute(ColRng, Chr$(N), vbNullString)
Next ‘N
Set ColRng = Nothing
End Sub

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Legare,

Since you mentioned it, take a look here. It must have taken a tremendous amount of work…

http://home.mchsi.com/~taxcalculator/

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Steve,

Very useful information. It even applies when using…

Cells.Find(what:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

I thought the above was impervious.

Thanks,

Jim Cone
San Francisco, CA

W.C. Fields,

Assign the text to a String variable and then put it in the footer.
Run the following and see how it looks….

Sub FixFooter()
Dim MyStr As String

MyStr = “&P of &N”
ActiveSheet.PageSetup.LeftFooter = MyStr

MyStr = “&P of &N” & vbCr
ActiveSheet.PageSetup.CenterFooter = MyStr

MyStr = “&P of &N” & vbCr & vbCr
ActiveSheet.PageSetup.RightFooter = MyStr
End Sub

Regards,

Jim Cone
San Francisco, CA

Hans,

The only thing I can think of would be if you are you using the same variable to collect all the answers.
Such as…

Response = Response & vbcr & Response

At about 20 to 24 lines, Msgboxes run out of room.
However, I have never seen them refuse to appear.

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

John,

Good idea. I didn’t think that both files would fit into 100KB, but they do.

The attached zipped file contains:

“Side by Side”.xla – 137kb
“Side by Side Read Me.doc – 31kb

The Read Me has instructions for installing the Excel add-in, along with some general info on the program.
For others not familiar with add-in files (.xla) – Do not attempt to open the xla file – read the Read Me file first.

Comments about, suggestions on the program are welcomed.

Regards,

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Fedorn,

The “Side by Side” program and one page Word.doc directions are on the way.
Anyone else interested?

Jim Cone
San Francisco, Ca
jim.coneXXX@rcn.comXXX

HUP,

I’ve written and Excel add-in “Side by Side” that does what you want.
It adds a new menu item to the Format menu and is easy to use and very quick.
It does not affect your data as a new worksheet is created with the side by side column arrangement.
You print from the new worksheet.
If you want to give it a try, I will be glad to email it to you.

Jim Cone
San Francisco, CA
jim.coneXXX@rcn.comXXX

Ray,

It might be the “F8” key was pressed.
That key toggles the extend selection feature in Excel.
If its on, it will show “Ext” in the right hand section of the status bar.

Regards,

Jim Cone
San Francisco, CA

Meredith,

With a little programing code you can.

Paste the following code into a workbook module ( Alt + F11).
Change the “xxx” password in the code to the actual password.
On the spreadsheet, use the View | Toolbars | Forms menu to add a button and assign the procedure (code) to it.
The code unprotects the sheet, shows a formatting dialog box and then protects the sheet.
If a problem were to occur with the code then a message is displayed that says to contact you.

‘Code follows———————————————————————————————————-

Sub MakeItPretty()
On Error GoTo GotUgly
ActiveSheet.Unprotect password:=”xxx”
Application.Dialogs(xlDialogPatterns).Show
ActiveSheet.Protect password:=”xxx”
Exit Sub
GotUgly:
Beep
MsgBox “Error ” & Err.Number & ” ” & Err.Description & vbCr & _
“Please see Meredith if the problem persists. “, _
vbExclamation, ” Format Error Alert”
On Error Resume Next
ActiveSheet.Protect password:=”xxx”
End Sub
‘—————————————————————

Regards,

Jim Cone
San Francisco, CA