CODEWITHSUNDEEP
vbams-access
Alex
Alex

Reputation: 37

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

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?

Upvotes: 0

Views: 1310

Answers (1)

Applecore
Applecore

Reputation: 4099

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,

Upvotes: 1

Related Questions