VBA Issue / Exporting Excel File to CSV Causing Double Quote Issues

[Laurence Harper]

Hi All,

My colleague and I have created some VBA code to transform a standard report into semi-colon-delimited data, all within column A, which is then imported into our financial systems (SAP).

SAP only handles numerical values in European format, therefore, as part of the transformation, we have had to convert values from #.## to #,## (where # represents a value).

When exporting this data to .CSV, this causes issues because lines that contain values (decimals are shown as commas) then force quotation marks at the start and end of the text string.

Is there a way around this? Please see below code.

Thank you very much in advance, we really appreciate it!

Laurence Harper

Systems & Technical Accounting Assistant

Sub BudgetUploadExecute(VersionNo, FiscalYear)
'
    
    CostReptWorkBook = ActiveWorkbook.Name
    CostReptTab = ActiveSheet.Name


    'Open a blank worksheet:
    Workbooks.Add
    UploadFile = ActiveWorkbook.Name: UploadSheet = ActiveSheet.Name      'Recording the name of the empty workbook and tab for future reference
    
    'Activate the report workbook
    Workbooks(CostReptWorkBook).Activate
    Worksheets(CostReptTab).Activate
    
    'Create first row (file information) - line 0, no delimiter at the end of data block
    Workbooks(UploadFile).Worksheets(UploadSheet).Cells(1, 1) = "0;8000;BUD;" & VersionNo & ";" & FiscalYear & ";1;12;GBP;P"
    
    'Create header
    Workbooks(UploadFile).Worksheets(UploadSheet).Cells(2, 1) = "1;/MRPI/LDGR;/MRPI/BUKR;/MRPI/KOST;/MRPI/KOAR;/MRPI/FBER;/MRPI/KSTR;/MRPI/LLOB;/MRPI/FFGR;/MRPI/PGSL;/MRPI/FDCH ;/MRPI/CTY;/MRPI/AUFT;/MRPI/BARE;/MRPI/FPRC;/MRPI/FOPP;/MRPI/CD1;/MRPI/FLD1;/MRPI/FLD2;/MRPI/FLD3;/MRPI/DUMMY;/MRPI/AMOUNT01;/MRPI/AMOUNT02;/MRPI/AMOUNT03;/MRPI/AMOUNT04;/MRPI/AMOUNT05;/MRPI/AMOUNT06;/MRPI/AMOUNT07;/MRPI/AMOUNT08;/MRPI/AMOUNT09;/MRPI/AMOUNT10;/MRPI/AMOUNT11;/MRPI/AMOUNT12;/MRPI/AMOUNT13;/MRPI/AMOUNT14;/MRPI/AMOUNT15;/MRPI/AMOUNT16;"
    
    'Create line items with delimiter at the end, transform values to European format
        
    CheckSum = 0    'This is needed to create the checking sum at the end
    
    Call LineItems(UploadFile, UploadSheet, CheckSum, LastRow)  'This procedure will copy the line items into the upload file
    
    'Create summary - no delimiter at the end of the row
    
    Workbooks(UploadFile).Worksheets(UploadSheet).Cells(LastRow, 1) = "9;" & NumEU_StringFormat(CheckSum)
    
    'Save file as semicolon delimited CSV file
    Workbooks(UploadFile).Activate
    
    'ActiveSheet.PrintAs Filename:="J:\FAS GT\Key Business Users only\Macro Files\CostPlanUploadV3.txt", FileFormat:=xlTextPrinter, CreateBackup:=False
    'ActiveSheet.SaveAs "J:\FAS GT\Key Business Users only\Macro Files\CostPlanUploadV3.csv", xlCSV
   
    ActiveWorkbook.SaveAs Filename:="J:\FAS GT\Key Business Users only\Macro Files\TestUpload.csv", FileFormat:=xlTextWindows, CreateBackup:=False
    
    
    'Close file
    
    'Inform user of CSV file location
        
    
End Sub


Sub LineItems(UploadFile, UploadSheet, CheckSum, RowToWrite)


    RowToWrite = 3


    For ReportRow = 4 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
        'Only for 8-codes
        AcctCode = Mid(Cells(ReportRow, 2), 4, 10)
        
        If Left(AcctCode, 1) = "8" Then
        
    
        'Add textual fields
            RowContent = "2;" & Cells(ReportRow, 5) & ";" & Cells(ReportRow, 7) & ";" & Cells(ReportRow, 10) & ";" & AcctCode & ";;;" & Cells(ReportRow, 6) & ";;" & Cells(ReportRow, 13) & ";;;;;;;;;;;;"
            
        
                    'Does it add a space for blank columns???
        
        'Add numeric fields
        
            For FieldNo = 29 To 44
            
                RowContent = RowContent & NumEU_StringFormat(Cells(ReportRow, FieldNo).value) & ";"
                
                CheckSum = CheckSum + Cells(ReportRow, FieldNo).value
                        
            Next
        
            Workbooks(UploadFile).Worksheets(UploadSheet).Cells(RowToWrite, 1) = Trim(CStr(RowContent))
            
            RowToWrite = RowToWrite + 1
        
        End If
    
    Next


End Sub


Function NumEU_StringFormat(value)


    NumEU_StringFormat = Replace(CStr(Format(value, "##0.00")), ".", ",")   'how many decimals, what if no decimals present?
    
End Function


Sub NumEU_StringFormat2()


    Selection.NumberFormat = "#,##0"
    Selection.Interior.ColorIndex = 43
    MsgBox (CStr(Selection(1, 1)))
    
    'NumEU_StringFormat = 18
    'Selection.NumberFormat = "#,##0"
    'Workbooks(TargetFile).Worksheets(TargetTab).Cells(TargetRow, FYColumn(NumCol, EffectiveDate)).Interior.ColorIndex = 43


End Sub
Sub Macro2()
'
' Macro2 Macro
'


'
    Windows("Book13").Activate
    ActiveWorkbook.SaveAs Filename:= _
        "J:\FAS GT\Key Business Users only\Macro Files\TestUpload.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End Sub
Sub Macro3()
'
' Macro3 Macro
'


'
    Windows("Book16").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, PrintToFile:=True, Collate _
        :=True, IgnorePrintAreas:=False
End Sub
Sub Macro4()
'
' Macro4 Macro
'


'
    Windows("CostPlanUploadV4.csv").Activate
    ActiveWorkbook.SaveAs Filename:= _
        "J:\FAS GT\Key Business Users only\Macro Files\CostPlanUploadV44.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
End Sub

Sub VersionNumber(Answer, YearFlg)


    If YearFlg Then
        Answer = Trim(CStr(InputBox("Please specify the fiscal year (format 20##)")))
    Else
        Answer = Trim(CStr(InputBox("Please specify the version number below (format #.##)")))
    End If
    
    Do
        If Not Acceptable(Answer) Then     'Validity check
        
        
            Answer = MsgBox("Invalid Answer", vbRetryCancel)
            
            If Answer = vbRetry Then
            
                If YearFlg Then
                    Answer = Trim(CStr(InputBox("Please specify the fiscal year (format 20##)")))
                Else
                    Answer = Trim(CStr(InputBox("Please specify the version number below (format #.##)")))
                End If
                'Answer = Len(VersionNo) = 4
            
            End If
            
        End If
    
    Loop Until Acceptable(Answer) Or Answer = vbCancel
    


End Sub


Function Acceptable(Answer)


    Acceptable = Len(Answer) = 4


End Function

Sub BudgetUpload70()
'
' This macro will create the budget upload CSV file from the standard SAP report. It should be stored in Nafisah's personal macro file in the future.
'


    Call VersionNumber(VersionNo, False)
    
    If VersionNo = vbCancel Then
    
       MsgBox ("Macro Terminated")
    
    Else
    
        Call VersionNumber(FiscalYear, True)
    
        If FiscalYear = vbCancel Then
        
           MsgBox ("Macro Terminated")
           
        Else
        
            Call BudgetUploadExecute(VersionNo, FiscalYear)
            
        End If
    
    End If
    
End Sub





[B]

[/B]