Macro Help... why does this error out?

[Aaron Russell]

This macro exports some info from excel to notepad... it'll right about 4
records successfully then it'll give me a "Run-time error'5': Inavalid
procedure call or arguement" on the line of code that reads "CellText =
..Text & Space(ColWidth - Len(.Text))" If someone has some spare time or is
bored please take a look and see what i have to do to fix this problem.


Function WriteFile(delimiter As String, quotes As Integer) As String
' dimension variables to be used in this function.
Dim CurFile As String
Dim SaveFileName
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalRows As Double
Dim TotalCols As Double

' Show Save As dialog box with the .TXT file name as the default.
' Test to see what kind of system this macro is being run on.
If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
Else
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Text Delimited Exporter")
End If

'Check to see if Cancel was clicked.
If SaveFileName = False Then
WriteFile = "Canceled"
Exit Function
End If
'Obtain the next fee file number.
FNum = FreeFile()

'Open the selected file name for data output.
Open SaveFileName For Output As #FNum

'Store the total number of rows and columns to variables.
TotalRows = Selection.Rows.Count
TotalCols = Selection.Columns.Count

'Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
Dim ColWidth As Integer
ColWidth = Application.RoundUp(.ColumnWidth, 0)
'Store the current cells contents to a variable.
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(ColWidth - Len(.Text)) & .Text
Case xlCenter
CellText = Space((ColWidth - Len(.Text)) / 2) & .Text &
_
Space((ColWidth - Len(.Text)) / 2)
Case Else
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
< CellText = .Text & Space(ColWidth - Len(.Text))
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< End Select
End With
'Write the contents to the file.
'with or without quotation marks around the cell information.
Select Case quotes
Case vbYes
CellText = Chr(34) & CellText & Chr(34) & delimiter
Case vbNo
CellText = CellText & delimiter
End Select
Print #FNum, CellText;

'Update the status bar with the progress.
Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
+ ColNum) / (TotalRows * TotalCols), "0%") & "Completed."

'Loop to the next column.
Next ColNum
'Add a linefeed character at the end of each row.
If RowNum <> TotalRows Then Print #FNum, ""
'Loop to the next row.
Next RowNum
'close the .prn file.
Close #FNum
'reset the status bar
Application.StatusBar = False
WriteFile = "exported"
End Function

 

[Dave Peterson]

Without setting up a test worksheet, I'd look at what was in these variables
when it blows up:

ColWidth - Len(.Text)

If it evaluates negative (maybe the text overflows to the next column or even
wraps in the cell), you could have trouble.

space() doesn't like negative numbers.

===
And I'd move this Dim to near the top:
Dim ColWidth As Integer

You're declaring it multiple times in that loop.