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

VBA Save/Move file to new location

VBA Save/Move file to new location

Save/Move file to new location. PriorFileLocation is where the file is currently stored and NewFileLocation is where you want the file to be saved/moved to.

 

PriorFileLocation = "X:\MyDrive\FI Matrix.xlsx"
 NewFileLocation = "Z:\Dylan\FI Matrix2.xlsx"
 Name PriorFileLocation As NewFileLocation

VBA insert Access table into Email Body

VBA insert Access table into Email Body

The below VBA will insert a Access Table into the body of an email message using an HTML table.  This will loop a designated Access table to create the HTML table for the body of the email.

 

Make sure to enable Microsoft Outlook 14.0 Object Library in the VBA window -> Tools -> References.

' Insert Access table in outlook Acccess Database
 Dim objOutlook As New Outlook.Application
 Dim objEmail As Outlook.MailItem
 Dim strLtrContent As String

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)

html = "<!DOCTYPE html><html><body>"
 html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
 html = html & "Dear {name}, <br /><br />This is a test email from Excel using VBA. <br />"
 html = html & "Here is sheet1 data:<br /><br />"
 html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
 
 html = html & "<thead><tr>"
 html = html & "<th>Name</th>"
 html = html & "<th>Email</th>"
 html = html & "<th>Phone</th>"
 html = html & "<th>Address</th>"
 html = html & "</thead></tr>"
 While Not rs.EOF
 html = html & "<tbody><tr>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field1") & "</td>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field2") & "</td>"
 html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rs("Field3") & "</td>"
 html = html & "</tbody></tr>"
 rs.MoveNext
 Wend
 html = html & "</table></div></body></html>"
 
 Set objEmail = objOutlook.CreateItem(olMailItem)
 objEmail.Subject = "Our address has changed."
 objEmail.HTMLBody = html
 objEmail.Display
'----'

Access VBA backup all tables in Database to Excel

Access VBA backup all tables in Database to Excel

This VBA code will backup all your Access tables to Excel.  Each Access table will be a new tab in the Excel file that is created.  Basically this code will copy all your access tables to Excel.

 Dim td As DAO.TableDef, db As DAO.Database
 Set db = CurrentDb()
 For Each td In db.TableDefs
    If Left(td.Name, 4) <> "msys" Then
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
       td.Name, "X:\MyDocuments\BackupTables" & Format(Now, "yyyy.mm.dd_hhmm") & ".xls", True, td.Name
    End If
Next

 


VBA SQL copy table from one database to another

VBA SQL copy table from one database to another

This VBA will copy a Access Table from one Database into another.  You will copy the below VBA into the current database that you want to transfer the table from.  Update the Tbl_Names and filepath for the database you're transferring the table to.

DoCmd.RunSQL "DELETE FROM Tbl_NameTransferTo IN 'X:\MyDocument\MyDatabase.accdb'"
DoCmd.RunSQL "INSERT INTO Tbl_NameTransferTo IN 'X:\MyDocument\MyDatabase.accdb' SELECT * FROM Tbl_NameTransferFrom"

VBA error types

VBA Error Types

Compile Errors:  Errors that are recognized by the VBA compiler which stops the macro before it begins running.  Compile error must be resolved before the macro can run.

Runtime Errors:  Error that occurs when macro is running.  Runtime errors stop the macro mid sequence and will allow you to click debug to check what line is causing the error.  Error handlers can be used to prevent the macro from stopping and debugging.

Logical Errors:  Nothing coded incorrectly so there are NO hardblocks that would prevent the macro from running.  However the logic of the macro may be incorrect causing an incorrect result.


VBA Error Handlers

On Error Resume Next – Will continue running macro regardless of errors.

On Error GoTo 0 – Resets Error handling to default setting.  Will debug on runtime errors.

On error GoTo ErrorHandlerName –  Will run the code thats under the ErrorHandlerName: when a runtime error occurs.

On Error GoTo ErrorHandlerName
Exit Sub

ErrorHandlerName:
 'Error handling code goes  here, on error macro will go to this section and run this error handling code.
End Sub

vba error handlers


VBA to loop all files in a folder

VBA macro that will open all Excel files in a folder

Macro that will open all Excel files in a folder.  This functionality can be used to aggregate multiple workbooks into one or pull data from all workbooks in a folder.  This demo example only opens the files.  Within the loop you will add your logic to be performed on the opened workbook.

https://www.vba-market.com/LoopAllFiles_inFolder.xlsm

Sub LoopAllFiles_inFolder()
MsgBox ("Select folder with files to loop")
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    On Error Resume Next
    strpath = .SelectedItems(1)
    Err.Clear
    On Error GoTo 0
End With
If Right(strpath, 1) <> "\" Then strpath = strpath + "\"
ChDir strpath
strextend = Dir("*.xls*")

Do While strextend <> ""
    Set wb1 = Workbooks.Open(strpath & strextend)
    wb1.Activate
    ' add logic to be performed on open workbooks here

strextend = Dir
Loop

End Sub


VBA loop HTML page elements by tag name

How to Loop webpage to find a specific element

This VBA code can be used to loop all the data on a webpage looking for a specific element.  This element can then be actioned.  Such actions as inputting value in text box, clicking button, scraping values, etc.  You have to lookup the name or ID in the HTML so you can tell Excel which element on the page to interact with.

https://www.vba-market.com/IEAutomationExample.xlsm

Sub scrape_loop()
Dim doc, hcol, text As Variant
Dim ie As SHDocVw.InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.Navigate ("https://www.google.com")
Do While ie.ReadyState <> READYSTATE_COMPLETE
    DoEvents
    Loop
    While ie.Busy
    DoEvents
    Wend
Set doc = ie.Document
Set hcol = doc.getElementsByTagName("input")
    For Each text In hcol
         If text.ID = "lst-ib" Then
              text.Value = "www.vba-market.com"
        End If
    Next
Application.Wait (Now + #12:00:02 AM#)
Set doc = ie.Document
Set hcol = doc.getElementsByTagName("button")
    For Each text In hcol
         If text.ID = "_fZl" Then
              text.Click
        End If
    Next
End Sub

Set hcol = doc.getElementsByTagName("button")

This should be set to the Tag type your looping such as input, button, td, etc.

The macro loops all the tags of the specified type.  You can then check if the name, id, innerHTML, etc of the element matches what you’re looking for.

If text.ID = "lst-ib" Then

If text.title = "Search" then

The ID, name, title, etc can be found in View Source of the webpage (right click inspect element).  Use the select tool to grab the element you’re looking for.

Then read through the HTML code to find the ID, name, title, innerHTML, etc.

search

Set doc = ie.Document
Set hcol = doc.getElementsByTagName("input")
    For Each text In hcol
         If text.ID = "lst-ib" Then
              text.Value = "www.vba-market.com"
        End If
    Next

Then use this to loop the tags to find the input Search box.  Once it is found the value “www.vba-market.com” is input into the Search box.

*Note:  Microsoft Internet Controls Reference must be enabled.