Skip to content

Instantly share code, notes, and snippets.

View jakelosh's full-sized avatar

Jake Losh jakelosh

  • Affirm
  • Pacifica, California, USA
  • X @jakelosh
View GitHub Profile
@jakelosh
jakelosh / vbaArraySlice
Created September 23, 2013 14:58
In Excel, I often like to transfer data ranges into arrays for processing. Sometimes I'm also interested in applying a function to each column of this array. After doing some digging I found that VBA doesn't really do "array slicing", i.e., you can't just reference a column of an array. The simplest solution I found was to use the Index workshee…
Public Sub ArraySlice()
Dim myArray() As Variant
With ThisWorkbook.Sheets(1)
myArray = .Range(.Cells(1, 1), .Cells(12, 6)).Value
End With
Dim temp(1 To 12) As Variant
Dim i As Long
@jakelosh
jakelosh / vbaKeepDuplicates
Created August 20, 2013 04:31
Excel 2010 already has the great new feature of being able to remove duplicates from a range of cells with the click of a button, but what if you want to keep the duplicates and remove the unique items? I got a question at work today about this, so I hacked this subroutine together.
Public Sub KeepDuplicates()
'Create one array for our raw data and one array for our duplicates
Dim varDataArr() As Variant, varDupesArr() As Variant
'Create some counters for our loops
Dim i As Long, j As Long, k As Long, count As Long
'Create a Boolean variable to tell us if we find a matching value
Dim bolMatch As Boolean
'Load the selection into our raw data array, varDataArr
@jakelosh
jakelosh / vbaMostRecentFileFunc
Created July 18, 2013 01:19
This function combs a file folder searching for the most recent version of a file and returns the file name as a string so you can pass it to Workbook.Open or whatever else you like. I'd seen other examples of subroutines that can open the most recent file in a folder, but I wanted to be able to a) Search folders that had lots of different files…
Public Function MostRecentFile(ByVal FileName As String, ByVal FolderName As String) As String
Dim objFileSys As FileSystemObject
Dim objFile As File
Dim objFolder As Object
Dim strFilename As String
Dim datFile As Date
Dim strDir As String
'Set file path
@jakelosh
jakelosh / vbaPdfToExcelSendKeys
Created July 5, 2013 17:02
I recently changed groups at work. In my new unit we process a lot of trades based off of paper trade tickets! In 2013! I was only in the group a day before I was on a mission to find a way to eliminate the paper. I was able to find the starting point for the below code from here: https://groups.google.com/forum/#!msg/excelvbamacros/hPZsy2QyFGM/…
Sub PdfToExcelSendKey()
Dim task
' open the file
' change the path of adobe program as per your desktop
task = Shell("C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe " & "C:\Documents and Settings\e531210\Desktop\test.pdf", vbNormalFocus)
' wait 2 secs
Application.Wait Now + TimeValue("00:00:2")
'send key to select all text
@jakelosh
jakelosh / vbaImportExcelWkstData
Created July 4, 2013 05:32
My supervisor at work was interested in archiving trades stored in Excel worksheets in one giant Excel database. I suggested using an Access database instead. What follows was based primarily on a response from from HansUp on StackOverflow.com to a user with a question similar to mine (http://goo.gl/D#EUk). This stub imports the data stored in t…
Public Sub ImportExcelWorksheetData()
Dim strPath As String
strPath = "C:\Documents and Settings\Some Folder\temp.xls"
DoCmd.TransferSpreadsheet acImport, , "tblTemp", _
strPath, True
End Sub
@jakelosh
jakelosh / vbaMakeTempWorkbook
Created July 4, 2013 05:28
I wrote this to format Excel worksheets so that they will be more readily importable into Access data tables.
Public Sub MakeTempWorkbook(ByVal FileName As String)
'Edit this if you need to change the path where the temp file is saved
Dim strTempLocation As String
strTempLocation = "C:\Documents and Settings\temp.xls"
'Declare our variables
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Dim xlWb As Excel.Workbook
@jakelosh
jakelosh / vbaSaveFiles
Created June 25, 2013 05:41
These subroutines work in tandem to copy all files and subfolders from FromPath to ToPath, which would typically be listed in an Excel spreadsheet. FromPath and ToPath must both contain strings of the file paths. These strings may be constructed via formula (e.g., in case the file path contains dates).The sub also requires a log worksheet so tha…
Public Sub Copy_Folder(ByVal FromPath As String, ByVal ToPath As String, ByRef LogSheet As Worksheet, ByVal i As Integer)
'Note: If ToPath already exists it will overwrite existing files in this folder
'if ToPath does not exist it will be made for you.
Dim FSO As Object
'Trim the paths just in case the user adds the \ at the end of the path
If Right(FromPath, 1) = "\" Then
FromPath = Left(FromPath, Len(FromPath) - 1)
End If
@jakelosh
jakelosh / vbaLastFunctions
Created June 24, 2013 16:58
Two special functions written to find the last row and last column of a given Excel worksheet. These get around the limitations of using properties like ActiveSheet.UsedRange.Rows.Count in that it will ignore columns that have been touched or formatted, but that have no values. Full Disclosure: The LastRow__lng function is not my own work but wo…
Public Function LastRow__lng(ByRef wksWorksheet As Worksheet) As Long
''Stores the last row with data on it
Dim lngLowestRow As Long
''Temp to hold the last row of each column
Dim lngTempRow1 As Long
Dim lngTempRow2 As Long
''Greater value of Dim lngTempRow1 & lngTempRow2
Dim lngColRange As Long
''Used as counter in loop
Dim objColumn As Object
@jakelosh
jakelosh / vbaOffAutos
Created June 24, 2013 16:43
This is a sub I wrote to turn off certain application features in Excel that I often toggle while running scripts. You feed it a bool and it toggles accordingly.
Public Sub OffAutos(ByVal bolState As Boolean)
If bolState = True Then
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.AskToUpdateLinks = False
End With
Else
@jakelosh
jakelosh / vbaToggleManualCalcs
Last active December 18, 2015 21:59
Another good macro for one's personal macro workbook. This one toggles between Manual and Automatic calculations. Hopefully your spreadsheets aren't so large that you need to use this often, but it prevents annoying autocalcs. I like to assign it to the keyboard shortcut CTRL + ALT + M.
Public Sub ToggleManualCalcs()
If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub