I've gotten my Access File pulling data from a database and turning it over into excel files that are auto generated and emailed. What I can't figure out is how to make the excel file have a message box pop up upon closing the excel file. I know this is possible to do as I've done it many times on regular excel files.
I believe that the issue is within the access file only generating .xlsx files and not .xlsm files. OR that the VBA code I am trying to use isn't proper (either in the placing of the code or the code itself.
If you can solve it and are wondering what should the message box say, I would simply like a "Have you complete the task?" Yes/No box nothing crazy.
FilePath = "\\ms000ew01\Departments\Reporting\Reports"
FileName = FilePath & "\" & GrName & ShipDate & Timestamp
Attachfile = FileName & ".xlsm"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Draft",
FileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Turn",
FileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Final",
FileName, True
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = False
.Workbooks.Open (FileName & ".xlsm")
.Sheets("Draft").Select
.ActiveSheet.UsedRange.Font.Name = "Tahoma"
.ActiveSheet.UsedRange.Font.Size = 8
xlApp.Cells.EntireColumn.AutoFit
xlApp.Cells.EntireRow.AutoFit
.Sheets("Turn").Select
.ActiveSheet.UsedRange.Font.Name = "Tahoma"
.ActiveSheet.UsedRange.Font.Size = 8
xlApp.Cells.EntireColumn.AutoFit
xlApp.Cells.EntireRow.AutoFit
.Sheets("Final").Select
.ActiveSheet.UsedRange.Font.Name = "Tahoma"
.ActiveSheet.UsedRange.Font.Size = 8
xlApp.Cells.EntireColumn.AutoFit
xlApp.Cells.EntireRow.AutoFit
Set xlApp = Nothing
Dim OutApp As Object
Dim MailObj As Object
Set OutApp = CreateObject("Outlook.Application")
Set MailObj = OutApp.CreateItem(olMailItem)
With MailObj
.To = EmailTo
.Subject = GrName & " Report"
.Body = "Attached is your report"
.Attachments.Add Attachfile
.Send
End With
Set OutApp = Nothing
Set MailObj = Nothing
rst.MoveNext
End With
End Sub
EDIT: The code listed below was updated on Monday 7/16/18 to reflect the work I've done on it since this original post last week. The new code is fully tested and functional in Office 2007, however it will need to be customized with the proper file names, tables, queries, email addresses, etc. In all, glad I finally got this working, since I've been wanting to programmatically generate Access forms & reports with embedded code.
I'll continue my comment here. To paraphrase, the Access VBA module would need to write new code into the Excel VBA module. In order to allow this, the Access VBA project would need a reference to VBIDE aka Microsoft Visual Basic for Applications Extensibility. This library allows fairly extensive access for automation of the VBA IDE aka the windows you write code in.
Chip Pearson (who sadly perished in June after a car accident in April) has an excellent collection of pages on coding for VBIDE: cpearson.com/excel/vbe.aspx. Unfortunately, I can now find no useful command references for VBIDE on any Microsoft Pages; they seem to have removed all but those for Office 365, and what's left isn't much. This follows on Microsoft's long tradition of under-documentation for VBIDE.
I'm currently in the process of working this out for a project of my own, with a twist. The code that needs to be written is the Click event of a button. And to write events with VBIDE (rather than a regular sub or function), a special method must be used: CreateEventProc. As I mentioned, my project isn't done yet, but I've hacked this code example together for you. Note this is not tested. I will see if I can make this actually work later today. We both seem to have the same goal of using Access VBA to create Excel workbooks and then write VBA into them, so I'm motivated to make it work...
Public Function CreateExcelWorkbookWithEvents()
'This procedure is meant to reside in a Microsoft Access code module.
'This procedure requires two project references:
' 1) Microsoft Excel XX.Y Object Library (mine is 12.0)
' 2) Microsoft Outlook XX.Y Object Library (mine is 12.0)
' 3) Microsoft Visual Basic for Applications Extensibility X.Y (mine is 5.3)
'Project references are always preferred over CreateObject() when possible, since a
'reference allows the IntelliSense auto-complete to do its job. Otherwise, it's
'coding blind, and that's just no fun.
'Access variables.
Dim acc As Access.Application
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsRows As Long
Dim sqlText As String
'Excel variables.
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim wss As Excel.Sheets
Dim ws As Excel.Worksheet
Dim ws1 As Excel.Worksheet
Dim ws2 As Excel.Worksheet
Dim ws3 As Excel.Worksheet
Dim firstCell As String
'VBIDE variables.
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Dim cmod As VBIDE.CodeModule
Dim code As String
'Outlook variables.
Dim olapp As Outlook.Application
Dim olmsg As Outlook.MailItem
'Other variables.
Dim filepath As String
Dim filename As String
Dim fileext As String
Dim fullFilename As String
Dim timestamp As String
'Filename construction.
filepath = "c:\windows\temp"
filename = "MyWb"
fileext = "xlsm"
timestamp = VBA.Format(Now(), "yyyymmddhhnnss")
fullFilename = filepath & "\" & filename & "_" & timestamp & "." & fileext
'Access objects.
Set acc = Access.Application
Set db = acc.CurrentDb
'Excel objects.
Set xl = New Excel.Application
'Create a new blank WB with one worksheet. The 'xlWBATWorksheet' parameter creates a
'new blank workbook with only one sheet instead of the usual three. A weird side
'effect of this is the workbook will have the name "Sheet1" instead of "Book1", but
'otherwise it's a perfectly normal workbook.
Set wb = xl.Workbooks.Add(xlWBATWorksheet)
'Uncomment and change text if desired.
'xl.Caption = "Workbook Title"
'Add & name the tabs.
Set wss = wb.Worksheets
Set ws1 = wss(1)
ws1.name = "tmpDraft"
Set ws2 = wss.Add(, wss(wss.Count), , xlWorksheet)
ws2.name = "tmpTurn"
Set ws3 = wss.Add(, wss(wss.Count), , xlWorksheet)
ws3.name = "tmpFinal"
ws1.Select 'Go back to the first sheet.
'Loop through worksheets, use tab names for the queries, dump the data into the sheets,
'then format them as desired.
firstCell = "A1" 'Where to put data on each sheet.
For Each ws In wss
sqlText = "SELECT * FROM " & ws.name & ""
Set rs = db.OpenRecordset(sqlText, dbOpenSnapshot, dbFailOnError)
rsRows = ws.Range(firstCell).CopyFromRecordset(rs) 'This
Set rs = Nothing
ws.Cells.Font.name = "Tahoma"
ws.Cells.Font.size = 8
ws.Cells.EntireColumn.AutoFit
ws.Cells.EntireRow.AutoFit
Next
'Add the event code. Build the code in a way that's easy to read. Because there are
'of embedded double-quotes in the strings, this part can get quite messy and
'difficult to read. So that's why VBA.Replace() is used. It makes the "code of code"
'much more freindly to human eyes. The code that's built here is only what's
'between Sub...End Sub, which are created automatically by CreateEventProc().
'
'Just as in Microsoft Word, the paragraph character indicates where a
'hard-return should be, but something offbeat had to be used to show the double
'quotes, so the degree symbol ° is used, that being Chr$(176). Although any
'character(s) which the programmer desires may be used, these were chosen because
'they do not appear as valid characters in VBA.
'
'Note: The section of the code below with the If-Then to detect if Excel is visible
'are needed because the wb.Close statement further down in this subroutine cause the
'event we just created to be trigged as if the user is attempting to exit Excel. This
'seemed to be the simplest way to handle this, with other options such as setting
'a global variable somehow in Excel while the code is being created, but I didn't\
'experiment with that.
'
code = ""
code = code & "Private Sub Workbook_BeforeClose(Cancel As Boolean)¶"
code = code & "Dim xlapp As Excel.Application¶"
code = code & "Dim msgResponse As VbMsgBoxResult¶"
code = code & "Dim msgTitle As String¶"
code = code & "Dim msgText As String¶"
code = code & "Dim msgStyle As Long¶"
code = code & "¶"
code = code & "'Detect if Excel is hidden, presumably because it was created via automation¶"
code = code & "'from another program. If so, do not prompt the user to confirm exit.¶"
code = code & "Set xlapp = Excel.Application¶"
code = code & "If xlapp.Visible = True Then¶"
code = code & " msgTitle = °Confirm Exit°¶"
code = code & " msgText = °Are you sure you want to exit?°¶"
code = code & " msgStyle = vbApplicationModal + vbExclamation + vbYesNo¶"
code = code & " msgResponse = MsgBox(msgText, msgStyle, msgTitle)¶"
code = code & " If msgResponse = vbNo Then¶"
code = code & " Cancel = True 'This is what cancels the Close event.¶"
code = code & " End If¶"
code = code & "End If¶"
code = code & "End Sub¶"
code = VBA.Replace(code, "¶", vbCrLf) 'Replace the ¶ characters with hard returns.
code = VBA.Replace(code, "°", VBA.Chr(34)) 'Replace the ° characters with double quotes.
'Dig into the VBA Project, create the event, and add the code. NOTE: There is an
'issue with manipulating code from VBA. You can't step through those lines in debug
'mode if you are adding code to the SAME file you're working in. For instance, if you're
'in an XLSM file, adding code to its own ThisWorkbook module. Won't work. It causes a
'runtime error. I think Chip Pearson mentioned it on his VBIDE pages, but I can't find it.
Set proj = wb.VBProject 'Grab the VBA project.
Set comp = proj.VBComponents("ThisWorkbook") 'Grab the "ThisWorkbook" code module.
Set cmod = comp.CodeModule 'Grab the ThisWorkbook code window.
cmod.InsertLines cmod.CountOfLines + 1, code 'Insert the code.
'Originally CreateEventProc() was used, but it was found to pop open the VBA IDE
'window, despite any attempt to prevent it. However, the same goal can be accomplished
'with the regular InsertLines() function. It's all text in the IDE anyways, and how it
'gets there doesn't matter. One need only be certain everything is spelled correctly.
'This is the original attempt:
'xl.vbe.MainWindow.Visible = False 'Hide the VBA editor from the user.
'firstLine = cmod.CreateEventProc("BeforeClose", "Workbook") + 1 'Create the event.
'Save as a macro-enabled workbook. Don't forget: each installation of Excel may need
'the macros enabled in the security settings.
wb.SaveAs fullFilename, xlOpenXMLWorkbookMacroEnabled
'Not sure if this is desired.
'xl.Visible = True
'Clear the variables. If the 'xl' variable is not released, lost instances of Excel
'will start to pile up in memory. They can be seen in Task Manager, but to be properly
'identified, click View > Select Columns > Command Line > Ok. The instances of Excel
'started from VBA will have the command line switch '/automation -Embedding' like this:
'"C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE" /automation -Embedding
'And even so, these instances of Excel may not unload from memory until this subroutine
'is finished and exits. It's a fussy thing with a difficult pattern to follow. I find if
'while devloping the code, when stepping through debug with F8, if I stop the macro
'prematurely, the automation instances tend to stack up and need to be manually killed.
wb.Close True
xl.Quit
Set wb = Nothing
Set xl = Nothing
'Create the email and send it.
Set olapp = New Outlook.Application
Set olmsg = olapp.CreateItem(olMailItem)
olmsg.To = "mshea@certobrothers.com"
olmsg.Subject = "Report"
olmsg.Body = "Attached is your report"
olmsg.Attachments.Add fullFilename, olByValue
olmsg.Send
Set olapp = Nothing
Set olmsg = Nothing
End Function