VBA Kill Task

VBA to kill task in task manager by process name.  You need to lookup the process name in task manager so you can tell excel which task to kill.  In the below example Excel is killing task Max.exe process.

 

Function TaskKill(sTaskName)
TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im " & sTaskName, 0, True)
End Function
TaskKill "Max.exe"

VBA Change Default File Prompt Folder

VBA Change Default File Prompt Folder

Change the default folder that opens when VBA prompts user to select a file.  First you need to change to the Drive where the folder is stored. Below example is using X: drive location. Then change directory to the folder you want to open for the user.

ChDrive "X:\"
ChDir "X:\Data\Dev\Files"

VBA to add all files in a folder to an email

Add all files in folder to an email

VBA that will add all files in folder to an email.  Loops the folder and attaches each file to the email.

 

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
 .To = "Dylan.Gregory@vba-market.com"
 .CC = "CC@email.com"
 .Subject = "Subject Line"
 .HTMLBody = "Add all attachements in folder … "
 strPath = "Z:\My Documents\tempFolder\"
 StrFile = Dir(strPath & "*.*")
 Do While Len(StrFile) > 0
 .Attachments.Add strPath & StrFile
 StrFile = Dir
 Loop

.display
End With

Access VBA to run an Excel Macro from Access

Access VBA to run an Excel Macro from Access

This VBA will run an Excel macro from Access.  The VBA exports a designated Access table to Excel and then runs a macro from Excel.  Allows you to manipulate data from an Access table using an Excel macro.  Then you can have the macro save the file, write back to the Access Table or anything else you can think of.

*Update the MySQL SELECT statement with your table name.

*Update MySheetPath with your Excel file

* Update Set XlSheet = XlBook.Worksheets("MasterList") with the Excel Sheet name where you want to import your Access table to.

*Update Xl.Run ("ExcelMacroName") with the name of the macro you want Access to run.

Dim MyRecordSet As New ADODB.Recordset
Set cnn = CurrentProject.Connection
MyRecordSet.ActiveConnection = cnn

MySQL = "SELECT Table1.* FROM Table1;"
MyRecordSet.Open MySQL

MySheetPath = "Z:\My Documents\FileName.xlsm"
MsgBox "Please open Excel. Once open, press the OK button below", vbOKOnly, "Open Excel"
Set Xl = GetObject(, "Excel.Application")
Set MyCurrentDb = CurrentDb
Set XlBook = GetObject(MySheetPath)

Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets("MasterList")

XlSheet.Range("A2").CopyFromRecordset MyRecordSet

XlBook.Activate

Xl.Run ("ExcelMacroName")

XlBook.Close (False)

VBA to have macro wait

Excel VBA wait:

Application.Wait (Now + TimeValue("00:00:02"))
 

Access VBA wait:

WaitUntil = Now + TimeValue("00:00:05")
 Do
 DoEvents
 Loop Until Now >= WaitUntil

 


Format number with leading zero's

One of the most efficient ways I've found to format a number with leading zero's.

Excel: =Right("000000000" & C2, 9)

Access:  Right("000000000" & [Tbl_All_Accounts]![AccountNumber],9)


Excel VBA get Access database table data

Excel VBA get Access database table data

This Excel VBA will connect to an Access database and loop the designated Table.  Update Data Source with the path to your Access Database and then update rs.Open"Table1" with your Table's name.

 

 

Sub GetDBData()

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Z:\My Documents\Database1.accdb;"
Set rs = New ADODB.Recordset
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
 rs.MoveLast
 rws = rs.RecordCount
 rs.MoveFirst
 
 
 Do While Not rs.EOF
 val1 = rs("Field1")
 MsgBox "Table1 value =" & val1
 rs.MoveNext
 Loop
 rs.Close



End Sub

Access VBA get record count in table

Access VBA to count the number of records in table.  Message box will say how many records are in table.

 

Dim db As DAO.Database
Set db = CurrentDb
strSQL = "SELECT Table1.* FROM Table1;"
 On Error Resume Next
 db.QueryDefs.Delete "CountList"
 On Error GoTo 0
 Set NewCountList = db.CreateQueryDef("CountList", strSQL)
 Set RstAcct = db.OpenRecordset("CountList")
 RstAcct.MoveLast
 x = RstAcct.RecordCount
 If RstAcct.EOF Then
 MsgBox "table is blank"
else
   msgbox "There are " & x & " number of records"
 End If

Access VBA Export table to excel

Access VBA Export table to excel

Access VBA to export a table to excel.  CreateQueryDef creates a temporary query to SELECT the data from the table and Docmd.TransferSpreadsheet exports the data to Excel.  Update xlFileSaveName with the path for where you want the Excel to save to and update the SELECT query "SQLMasterPage" with your table name.

Dim db As DAO.Database
Set db = CurrentDb
 On Error Resume Next
    db.QueryDefs.Delete "ExportTempQuery"
 On Error GoTo 0
 xlFileSaveName = "Z:\Dylan\export.xls"
 SQLMasterPage = "SELECT Table1.* FROM Table1"
 Set MasterPage = db.CreateQueryDef("ExportTempQuery", SQLMasterPage)
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "ExportTempQuery", xlFileSaveName, True

Access VBA import Excel file to temporary Access table

Access VBA import Excel file to temporary Access table

This VBA will import an excel file into Access and create a new table for it.  Change FullCurrFileLocation with the file path for your file.  You can change "Temp_Table" to be the name that you want your Access table to have.

 

 'Import Excel table to temporary access table'
 FullCurrFileLocation = "X:\Data\Dev\Fixed Income Matrix.xlsx"
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Temp_Table", FullCurrFileLocation, True