.csv file empty; why?

[alwayslrN]

I have code that in August when I created was working fine. For some reason today, the csv file that is created is empty. I have not changed anything to the code and when I step through the code things look like they work, but the file is empty.

Here is the code that calls the procedure that creates the csv.

Private Sub cmdCreateFile_Click()

    Dim ManualLastRow As Long
    Dim ManualLast As Integer
    Dim i As Integer
    Dim ManualFileName As String
    Dim rngError As Range
    Dim Ans As Integer
    Dim AnsPool As Integer
   
    Application.ScreenUpdating = False
    
    If wsNewLoans.Range("c2").Value = "" Then
        AnsPool = MsgBox("The pool number is missing, please add it to cell B9.", vbOKOnly, "Verify Pool Number Provided")
        Exit Sub
    End If
    
    ManualLastRow = Cells(Cells.Rows.Count, "b").End(xlUp).Row
    ManualLast = ManualLastRow
    
    ManualFileName = wsNewLoans.Range("c2").Value
    
    'use the answer to know whether or not to continue
    Ans = MsgBox("Is " & ManualFileName & " the correct pool number?", vbYesNo + vbQuestion, "Verify Pool")
    
        If Ans = vbNo Then
            Exit Sub
        Else

    Call RemoveLeadingZero
    Call SetDateAsText
    Call FormatValues
    Call FindReplace
    
    Range("a9" & ":am" & ManualLast).Select
    
    On Error Resume Next
    Set rngError = Range("a9" & ":am" & ManualLast).SpecialCells(xlCellTypeFormulas, 16).Value
    On Error GoTo 0

    If Not rngError Is Nothing Then
        MsgBox "There was an error in the file."
    Else

'        MsgBox "Things look good"

        Columns("R:T").EntireColumn.Hidden = False
        Call ExportToTextFile(ManualFileName)
        Columns("R:T").EntireColumn.Hidden = True
       
    End If

    Call ClearRecords
    wsNewLoans.Range("b9").Select
    
    End If
    
End Sub

Here is the code that creates the csv

Public Sub ExportToTextFile(ManualFName As String)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim FName As Variant
Dim Sep As String
Dim TextFileName As String

'Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With

FName = "P:\Input\SFP\LoanData\" & ManualFName & "_loans.csv"

Sep = ","

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = ""
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
'Application.ScreenUpdating = True
Close #FNum

End Sub

Any ideas? I am using Excel 2002 SP3

Thanks

 

[xlbo]

Youi are using "With Selection" to set the range to export but in that routine, you do not select anything.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[alwayslrN]

I just compared the spreadsheet that was used in August to the spreadsheet that is causing an issue today and one thing that did not work correctly is the validation of a formula causing an error. There is a #REF! error in for one of the records. The following code did not catch that for some reason.

    Range("a9" & ":am" & ManualLast).Select
    
    On Error Resume Next
    Set rngError = Range("a9" & ":am" & ManualLast).SpecialCells(xlCellTypeFormulas, 16).Value
    On Error GoTo 0

    If Not rngError Is Nothing Then
        MsgBox "There was an error in the file."
    Else

'        MsgBox "Things look good"

        Columns("R:T").EntireColumn.Hidden = False
        Call ExportToTextFile(ManualFileName)
        Columns("R:T").EntireColumn.Hidden = True

Geoff? When I did the step through the

Range("a9" & ":am" & ManualLast).Select

in the first procedure was still selected. I think that error I mentioned above may be the issue.

 

[AnotherHiggins]

Without examining your code - Why aren't you using Excel's built in "Save As" feature which allows you to select ".CSV" as the Type?

The code version of that process will look something like this:

ActiveWorkbook.SaveAs Filename:= _
    "C:\File\Path\FileName.csv", FileFormat:=xlCSVMSDOS

Is there any reason that won't work for you?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[alwayslrN]

John - I used the above code from another macro I have. For that particular macro the "Save As" did not work because I needed a certain number of commas at the end of the file and though I had selected a range, the Save As was not capturing thoses trailing commas.

My macro skills are not strong so I went searching the web to get the code I use to get the trailing commas.

In any case, the #REF! was the culprit. The person who uses the macro must have done a copy paste, which I told them not to do, and the formula, which is hidden, was messed up.

Thanks John and Geoff for your help.

I still need to figure out why the error was not recognized, however.

 

[stefen054]

hi

Set rngError = Range("a9" & ":am" & ManualLast).SpecialCells(xlCellTypeFormulas, 16).Value


may be u have to remove the ".value" at the above code.. so it will represent the range..

Not sure this could help you. Just a suggestion
Stefen