Convert Paypal exported CSV dates to NZ d/m/y format

Issue:

Transaction activity exports from paypal have dates in m/d/yyyy format. Opening in Excel set to NORMAL date formats (d/m/y etc) for the rest of the entire goddam planet format results in 2 issues which screw up importing into my excel cashbook (used for GST returns)

  • Dates recognised as dates are incorrect with m & d switched
    • so we see ‘4/01/2018’ for a source date of 1 Apr 2018 but it is actually the date value for 4 Jan 2018
  • Dates not recognised are just text
    • for 31 Mar 2018 we get text ‘3/31/2018

Need a formula to convert to correct excel date values that works across both errors above, and here is my version (assuming source date is in cell A4):

=IF(ISNUMBER(A4),DATE(YEAR(A4),DAY(A4),MONTH(A4)),DATEVALUE(MID(A4,FIND("/",A4,1)+1,FIND("/",A4,FIND("/",A4,1)+1)-FIND("/",A4,1)) & LEFT(A4,FIND("/",A4,1)-1) & RIGHT(A4,5)))

 

Excel with ActiveX controls failing with multiple VBA errors mostly 32809

Related to this sort of MS Security update:

http://support.microsoft.com/kb/3025036/EN-US

This worked for us:

  1. Open a blank version of excel.
  2. Disable ActiveX controls in Excel Options.

excel_disable_activex

  1. Open the offending file. (Click through any error received upon open)
  2. Save the file as a new name.
  3. Activate ActiveX controls.

So saving file without ActiveX enabled does something? anyway worked for us.

Excel VBA from shell command line

Dim objXL
Dim wsSheet
'-- The full path to the template file must be provided in the command line
'-- level eg c:\Temp\_excel> npfmacro.vbs c:\temp\_excel\npftemplate.xlsm
'-- where the npfmacro.vbs is located in the directory c:\temp\_excel
'-- the npftemplate.xlsm has a macro in the module called npf_errorasses which is 
'-- looking for a file called batch.xlsx to act upon.
On Error Resume Next
If WScript.Arguments.Count = 1 Then 
Set objXL = CreateObject("Excel.Application")
With objXL
 .Workbooks.Open (WScript.Arguments.Item(0))
 Set wsSheet = objXL.ActiveWorkbook.Worksheets(2)
 On Error GoTo 0
 If IsObject(wsSheet) Then
 .Application.Run "npf_errorasses"
 Else
 .Application.Run "npf_errorasses"
 End If
 .Application.Quit
End With
Set objXL = Nothing 
END If

Personal VBA

Lost old Personal.. so starting to collect new one..

Private Sub BorderRowOnTop(row As Long)

    With ActiveSheet.Rows(row).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
   
End Sub


Private Sub ColourRow(row As Integer, colourSet1 As Boolean, inSelectionOnly As Boolean)

    Dim r As Range
    If inSelectionOnly Then
        Set r = ActiveSheet.Range(ActiveSheet.Cells(row, Selection.Column), ActiveSheet.Cells(row, Selection.Columns(Selection.Columns.Count).Column))
    Else
        Set r = ActiveSheet.Rows(row)
    End If

    With r.Interior
        If colourSet1 Then
            If row Mod 2 = 0 Then
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            Else
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent4
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End If
        Else
            If row Mod 2 = 0 Then
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            Else
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent3
                .TintAndShade = 0.599993896298105
                .PatternTintAndShade = 0
            End If
        End If
    End With
End Sub


Sub BorderRowsOnChange()

'   For a selected column, border rows when data changes

    Dim c As Range
    Dim sel As Range
    
    Dim val As String
    
    Set sel = Selection
    
    ' Border top row
    BorderRowOnTop (Selection.row)
    ' Get initial value
    val = Selection(1, 1).Value
    
    For Each c In sel
        If c.Value <> val Then
            val = c.Value
            BorderRowOnTop (c.row)
        End If
    Next c

End Sub

Sub ColourRowsInSelectionOnlyOnChangeInActiveColumn()

'   For a selected column, alternate coloured rows when data changes
'   Only alternate within selected range

    Dim c As Range
    Dim sel As Range
    
    Dim val As String
    Dim colourSet1 As Boolean
    
    Set sel = Selection
    colourSet1 = True
    colourSubSet1 = True
    
    ' Border top row
    BorderRowOnTop (Selection.row)
    ' Get initial value
    
    val = ActiveCell.Value
    Call ColourRow(ActiveCell.row, colourSet1, True)
    
    For Each c In sel
        If c.Value <> val And c.Column = ActiveCell.Column Then
            val = c.Value
            colourSet1 = Not (colourSet1)
        End If
        Call ColourRow(c.row, colourSet1, True)
    Next c

End Sub

Sub ColourCompleteRowsOnChange()

'   For a selected column, alternate coloured rows when data changes

    Dim c As Range
    Dim sel As Range
    
    Dim val As String
    Dim colourSet1 As Boolean
    
    Set sel = Selection
    colourSet1 = True
    colourSubSet1 = True
    
    ' Border top row
    BorderRowOnTop (Selection.row)
    ' Get initial value
    
    val = Selection(1, 1).Value
    
    For Each c In sel
        If c.Value <> val Then
            val = c.Value
            colourSet1 = Not (colourSet1)
        End If
        Call ColourRow(c.row, colourSet1, False)
    Next c

End Sub


 

MS Excel custom function generate random strings

Pinched from someone else, (needs additional character sets, e.g. mixed case, text & numbers)

    Dim Rand As String
    Dim i As Integer, RndNo As Integer, XSet As Integer
    Dim MyCase As Integer
     
    Application.Volatile
    Select Case MySet
    Case Is = "1" 'Upper case
        MyCase = 65: XSet = 26
    Case Is = "2" 'Lower Case
        MyCase = 97: XSet = 26
    Case Is = "3" 'Leading Capital
        MyCase = 97: XSet = 26
    Case Is = "4" 'Text digits
        MyCase = 48: XSet = 10
    Case Is = "5" 'Numeric digits
        MyCase = 48: XSet = 10
    End Select
     
    If MySet = 3 Then 'Set leading character of "Name"
        i = i + 1
        Randomize
        Rand = Rand & Chr(Int((26) * Rnd + 65))
    End If
     'Set random length of string
    RndNo = Int((MaxLen + 1 - MinLen) * Rnd + MinLen)
    Do
        i = i + 1
        Randomize
        Rand = Rand & Chr(Int((XSet) * Rnd + MyCase))
    Loop Until i = RndNo
    RandomString = Rand
     'Convert string to number
    If MySet = 5 Then RandomString = RandomString * 1
     
End Function