Hi All,
I'm new to this forum and also to VBA. I have managed to create macros to do whatever I need to do from recording or finding bits of code online, but now I'm stumped with what I think is a very easy problem to solve - if only I knew the write letters to type (isn't that always the case?)
I have written a macro in outlook that asks users if they are going to use a particular instrument each day (it's an Auto Open macro so every day when a user opens outlook the question is asked). I have also made a tally in Excel that tallys their response (either yes or no).
The macro works perfectly and the tally works fine too, but I can't figure out how to close the Excel application from within the macro. I have a line at the end that closes the file.
sourceWB.Save
sourceWB.Close
And this closes the file within Excel and leaves the Excel application open. But I would like the Excel application itself to close. The following...
closes Outlook, but I don't want Outlook to close. All I want is to close Excel. In other words, I don't want people being left with an open Excel application when they open their Outlook. When the macro runs I want everything to be as it was before the macro started.
Can somebody please help?
Many thanks in advance.
Kind regards,
Andre
P.S. Here's the full code...
Dim xlApp As Object
Dim sourceWB As Workbook
Dim sourceSH As Worksheet
Dim strFind As String
Dim RngFound As Range
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = False
End With
strFile = "T:\Analytical\_Non Project\System Usage\Usage Monitor\Tally.xls"
Set sourceWB = Workbooks.Open(strFile)
Set sourceSH = sourceWB.Worksheets("Sheet1")
sourceWB.Activate
strFind = Environ("Username")
Set RngFound = Columns("A").Find(What:=strFind, After:=[A1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If RngFound Is Nothing Then
Exit Function
Else
RngFound.Cells.Activate
End If
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Value + 1
sourceWB.Save
sourceWB.Close
Bookmarks