Exporting an Excel spreadsheet to a csv using a VBA macro RRS feed

  • Question

  • Since upgrading to 2010 the macro gives an error "Expression to complex"

    I have a spreadsheet which the user updates then once done the press a button called "Create csv file"

    For some reason since the upgrade the macro does not work. Please help

    Public Sub ExportToCSCFile()
    Dim fileCreateObject As Object
    Dim fileObject As Object
    Dim filesysobject As Object
    Dim stream As String

    Dim fileCount As Double
    Dim columnCount As Double
    Dim ExportFileName As String
    On Error GoTo err_Handler:

        ExportFileName = GetExportFileName
        If Trim(ExportFileName) = "" Then
            MsgBox "Invalid file Destination", vbCritical, "Export File"
            Exit Sub
            Set fileCreateObject = CreateObject("Scripting.FileSystemObject")
            Set fileObject = fileCreateObject.CreateTextFile(ExportFileName, True)
            For fileCount = 9 To Range("A65536").End(xlUp).Row
                stream = ""
                columnCount = 1
                While Not IsEmpty(Cells(fileCount, columnCount))
                    stream = stream & Cells(fileCount, columnCount) & ";"
                    columnCount = columnCount + 1
                fileObject.writeline stream 'write line
            Next fileCount
        End If
        MsgBox ExportFileName & " was Exported successfully", vbInformation
        Set filesysobject = Nothing
        Set fileObject = Nothing

        Exit Sub

        MsgBox Err.Description, vbCritical, "Export To CSV file"
    End Sub

    Private Function GetExportFileName() As String

    Dim ExportFileName As String
    Dim MySaveName As String

    On Error GoTo err_Handler:

        ExportFileName = Application.GetSaveAsFilename("", _
                                                    fileFilter:= _
                                                    "Comma Delimited File (*.csv), *.csv,")
        If Trim(UCase(ExportFileName)) = "FALSE" Then
            GetExportFileName = ""
            GetExportFileName = ExportFileName
        End If
        Exit Function

        GetExportFileName = ""
        MsgBox Err.Description, vbCritical
    End Function

    • Moved by Carl Cai Thursday, November 27, 2014 2:37 AM more related
    Wednesday, November 26, 2014 6:23 AM


All replies