Category: Excel

Convert US Dates to Normal

Los estúpidos gringos americanos use MDY, why? WhoDaFuckKnows(tm) So getting dates from paypal statement exports (MDY) into the rest of the world (DMY) format: Excel: convert dates currently in US m/d/y format to UK / NZ / AU / Europe etc etc etc d/m/y: Insert a column to the right of the MDY dates column […]

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 & […]

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 […]

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 […]

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 […]