Category: Excel VBA

Excel VBA

Microsoft Excel: Saving Currency Values in CSV Files

Missing digit in CSV File Currency Values

I searched all over and did not find the right answer so I played around until I had found it myself and now I’ll share it with you.

I had picked up a VBA coding project from a contractor who was leaving.
The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.

The application takes a bunch of Excel data in various complex formats and creates a comma separated value file (CSV) as output that are used for import into a custom object in Salesforce.

One of the issues the customer was reporting was that they had an expectation that currency value in exported CSV files should retain the 2 digits after the decimal point.

The application was sending the output result in a CSV file but the currency value were only retaining one digit after the decimal if the last decimal was a zero.

A value of 91.20 is coming out as 91.2, missing the 2nd digit.

This happens consistently while using Excel with CSV files but not with XLS. It drops the zero every time.

How to Save currency values in a CSV File using Microsoft Excel

  1. Save your CSV file in MS Excel format with a .xlsx or .xls extension.
  2. Update the “Format Cells” to display the column as Currency. I set the option not to display a $ for currency and 2 digits after the decimal point.
  3. Save this file with a .CSV extension.  Your currency values with the 2 digits after the decimal point are preserved during this conversion.

That is it!

To validate it, open the CSV file with a text editor like Notepad or Notepad++ and you will see the number format with 2 digits after the decimal was retained.

Watch out!  If you open the CSV file with MS Excel and save it again as CSV, Excel will wipe out your currency formatting. 

Hope this helps somebody. 

Excel VBA: Delete all worksheets except for specific ones you want to keep

Excel VBA code example: How to delete all worksheets except for specific ones ones you want to retain.

This came from real world need today so I pounded this code out.

  • I have a list of worksheets that I want to keep. 
  • I run a process that adds analyzed data from another file to a new “temp” worksheet (tab), and additional worksheets per vendor from parsing out the imported data in the temp worksheet.
  • I can end up with 10 new tabs of analyzed data including the temp sheet.
  • The code below deletes all worksheets not on a “keep sheet list” . Basically, resetting the spreadsheet back to the state it was in before I ran macro to import and process that data that added all the new sheets.

Excel VBA Example: ResetWorkbook Function

'Reset Workbook
'Author Rick Cable
'Date: 11/28/2018
'Title: ResetWorkbook
'Purpose:   Deletes any workbooks not on the keep sheet list,
'           resetting it to previous state if you've added them
'           from some other process and want to undo
'Version 1.0
Public Sub ResetWorkbook()
    Dim sht As Worksheet
    Dim arrKeepSheetList As Variant
    Dim strKeepSheetList As Variant
    Dim isOnList As Boolean
        
    Application.DisplayAlerts = False
    isOnList = False
    strKeepSheetList = "Instructions,Template,Sample CSV File,Data Elements,Config"
    arrKeepSheetList = Split(strKeepSheetList, ",")
    
    'Loop through each worksheet in ActiveWorkbook
     For Each sht In ActiveWorkbook.Worksheets
        isOnList = False
        'Look thru list of sheets to keep
        'if sheet is not on list, delete it
        For Each Item In arrKeepSheetList
            If sht.Name = Item Then
                isOnList = True
                'If isOnList Then
                '    Exit For
                'End If
            End If
        Next Item
            
        If isOnList Then
             'MsgBox (sht.Name & " is on the list")
        End If            
        
        If isOnList = False Then
            'MsgBox ("I would be deleting " & sht.Name & " right now!")
            Worksheets(sht.Name).Delete
        End If
        
    Next sht

    Application.DisplayAlerts = True

End Sub