Returning the focus to Excel

[Tom Joseph]

I am running a series of VBA processes, then publishing/saving some PDFs and
finally emailing the PDFs. The code should then run aonther series of
processes. It seems to stall because Excel has lost the focus.

How to I return the focus to Excel or the workbook?

Thanks.

 

[CurlyDave]

I am running a series of VBA processes, then publishing/saving some PDFs and
finally emailing the PDFs.  The code should then run aonther series of
processes.  It seems to stall because Excel has lost the focus.

How to I return the focus to Excel or the workbook?

Thanks.

Where does it lose focus in the code?

 

[Mike H]

Hi,

That's too much of an open question. Post some code.

Mike

 

[Tom Joseph]

The focus is lost in the Sub Mail_ActiveSheet_PDF_Outlook which is creating
some PDF files and creating an email with the PDF attachemnts.

After the email is created, Excel is blinking in the taskbar and does not
proces the next report set till I click on Excel.

In case it is relevant, the Sub ProcessReportSetnn is loading a progress bar
and then calling a Sub that generates some reports.




Sub CreateAndEmailReports()

Call ProcessReportSet01
Sheets("Report").Select
Call Mail_ActiveSheet_PDF_Outlook

Call ProcessReportSet02
Sheets("Report").Select
Call Mail_ActiveSheet_PDF_Outlook

Call ProcessReportSet03
Sheets("Report").Select
Call Mail_ActiveSheet_PDF_Outlook

End Sub


Sub Mail_ActiveSheet_PDF_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim FilenameStr As String

If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then

FilenameStr = Application.DefaultFilePath & "\" & _
Format(Now, "dd-mmm-yy h-mm-ss") & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FilenameStr, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"See the attached PDF file with the last figures" & vbNewLine & _
vbNewLine & "Regards Tom"

On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Subject line"
.Body = strbody
.Attachments.Add FilenameStr
.Send 'or use .Display
End With
On Error GoTo 0

'Delete the pdf you send
Kill FilenameStr

Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "PDF add-in Not Installed"
End If
End Sub


Sub ProcessReportSet01()
UserForm01.LabelProgress.Width = 0
UserForm01.Show
End Sub

 

[Matt S]

How about just putting

Windows(your excel file name here).Activate

before the code needs to go back to excel?

 

[Tom Joseph]

Thanks, Matt.

I will give that a try.

Would there be a more general way. I change the excel name frequently to
include a version number. Perhaps Excel as the overall application?

 

[Matt S]

I do something like the following, cause I do the same in my macros:

'the following goes before you take the code to something other than excel
CurrentWorkbook = ActiveWorkbook.Name

'Then when you need to return to excel do:
Windows(CurrentWorkbook).Activate

That'll take care of multiple excel names.

 

[Peter T]

If you mean you want bring Excel to the front and activate, and with
whatever was previously active in Excel -

Appactivate application.caption

Regards,
Peter T

 

[Tom Joseph]

Thanks,

If you mean you want bring Excel to the front and activate, and with
whatever was previously active in Excel -

Appactivate application.caption

Regards,
Peter T

 

[Tom Joseph]

I apprecitate the help.

I do something like the following, cause I do the same in my macros:

'the following goes before you take the code to something other than excel
CurrentWorkbook = ActiveWorkbook.Name

'Then when you need to return to excel do:
Windows(CurrentWorkbook).Activate

That'll take care of multiple excel names.