MS Access macro to export file as .xlsx to specific filepath, and add current date (YYMMDD) to end of file name

0
votes

I'm thinking this might have to use VBA, but is there any way to create the following sequence of actions within the built-in MS Access macro features?:

  1. Run delete query for table (table1)
  2. Run append query for table1
  3. Table1 is exported where the following are true:
    • table1 is exported as .xlsx
    • the date is added to the end of the file name (table1_200414.xlsx)
    • the file is exported to a specific file path

I've seen step #3 done with VBA, but I'm wanting to be able to copy this macro between databases, so I don't know if the VBA code would be copied by a simple copy-paste of the macro. If it is, then how would you do this in VBA?

1
vbams-access

1 Answers

1
votes

The best way to do this is within VBA, not just because I think that step 3 can only be done using VBA, but also because you get error handling. And also, if you use in line SQL statements to perform your deletes/appends, you don't need to worry about copying extra queries over to another database - you just copy over the procedure.

Here is a short VBA procedure that performs all 3 steps for you:

Sub sExportData()
    On Error GoTo E_Handle
    Dim strFolder As String
    Dim strFile As String
    Dim strID As String
    CurrentDb.Execute "DELETE * FROM [Table1];"
    CurrentDb.Execute "INSERT INTO [Table1] SELECT * FROM [TableAppend];"
    strFolder = "J:\downloads\"
    strID=DLookup("ID","Table1")
    strFile = "Table1_" & strID & Format(Date, "yymmdd") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table1", strFolder & strFile, True
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sExportData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

Without knowing exactly what you are doing, it may be that you don't need to do the delete/append to Table1. It may be possible to output the data selected in the append query directly to an Excel file.

Regards,