Category Archives: Code Snippets

File System Object Code (last modified, etc.)

Get File Last Modified Date Function GetLastModDate(strFilePathAndName As String) As Date ‘USAGE: ‘ Immediate Window: ?GetLastModDate “C:\Temp\MyFile.xls” ‘ Returns: 6/22/2010 4:37:46 PM Dim fso As Object Dim f As Object Set fso = CreateObject(“Scripting.FileSystemObject”) Set f = fso.GetFile(strFilePathAndName) GetLastModDate = … Continue reading

Export A Table Or Query To Excel – Specific Worksheet on Specific Workbook

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t already have one. Public Function SendTQ2XLWbSheet(strTQName As String, … Continue reading

Export A Table Or Query To Excel – To Specific Worksheet

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t already have one. Public Function SendTQ2ExcelSheet(strTQName As String, … Continue reading

Export Table/Query To Excel to New Named Sheet

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t already have one. Public Function SendTQ2ExcelNameNewSheet(strTQName As String, … Continue reading

Export A Table Or Query To Excel

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t already have one. Public Function SendTQ2Excel(strTQName As String, … Continue reading

Export a Form’s Recordset to Excel – To an Existing Worksheet

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t already have one. You can then call it … Continue reading

Export A Form’s Recordset To Excel

Copy this code into a standard (not form or report) module. Name the module something other than this function name and make sure to set a reference to DAO if you don’t have one set already. You can then call … Continue reading

Clear All Controls On Form

Function ClearAll(frm As Form) Dim ctl As Control For Each ctl In frm.Controls Select Case ctl.ControlType Case acTextBox ctl.Value = “” Case acOptionGroup, acComboBox, acListBox ctl.Value = Null Case acCheckbox ctl.Value = False End Select Next End Function And then … Continue reading

Compact Database

ACCESS 2000 – 2003 Public Sub CompactData() ‘ you must make sure all objects are closed before trying to compact Dim frm As Form Dim rpt As Report For Each frm In Forms DoCmd.Close acForm, frm.Name, acSaveNo Next frm For … Continue reading

Code To Loop Through A Recordset

With this code you can modify it as you like. This is a simple starter for opening a DAO Recordset and looping through it. Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset(“TableOrQueryNameHere”) … Continue reading