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 retain

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!")
        End If
    Next sht

    Application.DisplayAlerts = True

End Sub

Excel VBA: Find Next Empty Cell in Row, When only Row 1 is Populated

It is Saturday and I’m about to go out and mow my lawn but I had an Excel VBA problem I wanted to get past first.

Not sure this is the best solution, but it is the one I came up with so I’ll share it with you now before I go mow.

Problem: I have a worksheet that is an event log for other subroutines. When created, the worksheet had a header row also created with data in Row 1 across columns A thru G.

When “WriteToEventLog” function call is firing off, we’re checking for next empty row in column A and cell “A1” already has header data written to it.

The method below works when cell “A2” is the starting cell, but fails when cell “A1” is used as the starting Range.

This code fails in an infinite loop and Out of Memory Error when starting from cell “A1” and ends up selecting all the cells memory can hold from column A.

        ActiveCell.Offset(1, 0).Select

Here is my Saturday morning, pre mow, attempt to solve this issue with a custom Excel VBA function that correctly finds the next empty cell when row 1 contains the header row.

Please email me if you read this and it has helped you in any way.

1st Pass Code:

Sub AddItemToEndOfList()
    'We assume that row 1 is already filled out from first process
    'Check to see if cell A2 is empty, if so then enter the next value in A2.
    'if not then go to next empty cell and enter the value.
    If IsEmpty(Range("A2").Value) Then
        ActiveCell.Value = ""
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = ""
    End If
End Sub

This can all be replaced with one line of code…

2nd Pass with Improved Code:

Range("A2").End(xlDown).End(xlUp).Offset(1, 0).Select

This works every time when we know Row 1 is already populated.

VBScript – Adding Days and Months to a Date Using the DateAdd Function

VBScript has a built in function called “DateAdd” that specifically deals with adding or subtracting when dealing with a date/time variable.

To use it you will need to know the syntax and parameters that it needs to make the magic happen.

For my example I’m going to get the current date and time of the system by calling the “Now()” function which grabs the date and time from the PC the script is being run on.

To add month to today’s date:

DateAdd("m",3, Now())

Since I wrote this code in March it should return a numeric “6” for June.  To spell out the month you would have to due some more work and maybe I’ll cover that in another post later.  The important thing to remember when working with dates and times using DateAdd, all your output will be numeric.

Calling the DateAdd Function in VBScript

The VBScript function, DateAdd, needs a couple of parameters separated by commas to make it work.

1. DateAdd needs to know the “Interval” parameter which is the part of the date your are wanting to work with.  In the sample above I used “m” to tell it that I want to add to the month. 

HINT: Don’t forget to wrap interval parameter text in double quotes.

2. DateAdd needs the number you want to add to the date and in my sample I used “3”. If you want to get a previous month then simply give DateAdd a negative number.

3. DateAdd needs to know what date you are working with. In my sample I used Now() to the current system date/time. You could have passed the date via a variable.

Where I had used the string value “m” to add to the month, you only have to change to interval parameter to change the year, month, day, hour, minute or second.  Here is a list of all the interval parameters you can pass to the DateAdd function:

  • yyyy – Year
  • q – Quarter
  • m – Month
  • y – Day of year
  • d – Day
  • w – Weekday
  • ww – Week of year
  • h – Hour
  • n – Minute
  • s – Second

I know for some people the DateAdd function is like preschool but for people who are new to VBScript I know it helps a lot if someone stops to explain the how and why of how this stuff works.

Good luck and happy coding!

VBScript: Adding Leading Zeros to a Date

I was recently developing a script to loop through an excel file and write the contents in to a text file.  One of the specifications was that the output dates had to have leading zeros like 01/01/2021 but the excel file had them as 1/1/2021.

I thought there must a VBScript function like the built in CDate for or FormatDateTime but neither of these seemed to return dates with leading zeros so I wrote my own function to do it.

As in all programing, there are many ways to write this to get the same output but this way worked for me on the project I used it for.  

VBScript FixDate Function to fix leading zeros in a date value

Function FixDate(strDate)
Dim iTemp, arrDate, item, strTemp

 ' my custom date fix function
 ' split the date in to an array by the "/" character
 ' check each date item and check to see if it is less than 1000
 arrDate = Split(strDate,"/")
 for each item in arrDate
  if len(item) < 2 then
   item = "0" & item
  end if
  ' Next section makes sure there is no / at the end of the rebuilt date when I put it back together.
  if item < 100 then
   strTemp = strTemp & item & "/"
   strTemp = strTemp & item
  end if
 ' Put the date back together
 FixDate = strTemp
End Function

How to call this function


How to Concatenate a String using VBScript

How to Concatenate a String Value in VBScript

To concatenate string values using VBScript we use the ampersand (&) character to piece together our composite string value.

Code Example:

Srting1 = "Word 1"
String2 = "Word 2"
String3 = String1 & String2
String3 = "Word 1" & "Word 2"

The output value would be “Word 1 Word 2”.

If you’re more familiar with JavaScript, you might be used to concatenating strings using the plus character (+).

In VBScript you can only use the + operator on numeric values.