none
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
        Else
       
            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
                Wend
                fileObject.writeline stream 'write line
            Next fileCount
       
        End If
           
        MsgBox ExportFileName & " was Exported successfully", vbInformation
       
           
        Set filesysobject = Nothing
        Set fileObject = Nothing

        Exit Sub

    err_Handler:
        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 = ""
        Else
            GetExportFileName = ExportFileName
        End If
       
       
        Exit Function

    err_Handler:
        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

Answers

All replies