macro not running - Code hang

I do have one report with macro. Previously it was runnign fine with one Private Sub (). This macro is suppose to run after refresh. So I have after refresh adn code is place under thisdocument. But when I go to Tools --> macro —> letter t is comign as macro name. if I click on edit button it will open a blank modeule. I have palced my code under this document. Am i doing soemthign wrong here…
I added one more procedure in bottom and now I am callign that in first.

When I click on refresh button of report. It is refreshign well and then after macro is being lunched but looks like when it is calling first time Emailsend procedue, it is getting hang.

Please help me out here

Private Sub Document_AfterRefresh()

On Error GoTo error_handler

'Define Variables*******

Dim Doc As Document
Dim Rep As Report
Dim i, j, k, m, n As Integer
Dim t As Integer
Dim ExcelDoc As String 'Stores the name of the Excel document to copy all report tabs to
Dim OutputPath, InputFilePath, StagingPath, Root, LogFilePath As String 'Stores the path in while the Excel files are created.
Dim myFilterVar As DocumentVariable
Dim e, intNumChoices As Integer
Dim myFilterChoices As Variant
Dim strNextValue As String

Call EmailSend(XYZ)
myvariable = “Ram”
LogTitleFlag = “Yes”
'Save report tabs as text files
Set Doc = ActiveDocument
Set myFilterVar = Doc.DocumentVariables(“Ram”)
intNumChoices = UBound(myFilterVar.Values(boUniqueValues))
myFilterChoices = myFilterVar.Values(boUniqueValues)

For e = 1 To intNumChoices
StartTime = Time
Set Rep = Doc.Reports.Item(1)

' Get the variable value
strNextValue = myFilterChoices(e)
ErrorText = ("Error in Process - Detail is printed below: " & vbCrLf & "Report Name = " & Rep.Name & vbCrLf & "Ram = " & strNextValue & vbCrLf)
Flag = "No"
For j = 0 To k
If strNextValue = ManagerSSOIDVar(j) Then
Flag = "Yes"
Exit For
End If
Next j

If Flag = “Yes” Then

MkDir (StagingPath & strNextValue)
DirName = (StagingPath & strNextValue & “”)

ExcelDoc = (strNextValue & “_” & mDate)

' build filter
Rep.AddComplexFilter myFilterVar, "= <SSOID4> = " &amp; """" &amp; strNextValue &amp; """"

' recompute the report
Rep.ForceCompute

If Dir(DirName & Rep.Name & “.htm”) <> " " Then
Kill DirName & Rep.Name & “.htm”
End If
Rep.ExportAsHtml (DirName & Rep.Name & “.htm”)

Set vbExcel = CreateObject(“Excel.Application”)
With vbExcel

'Create a new workbook to import text files into
If Dir(DirName & strNextValue & " For " & mDate & “.xls”) <> " " Then
Kill DirName & strNextValue & " For " & mDate & “.xls”
End If
.Workbooks.Add

.ActiveWorkbook.SaveAs DirName & strNextValue & " For " & mDate & “.xls”
error_handler:

If Err.Number = 53 Then Resume Next
Public Sub EmailSend(Body As String, Subject As String, LogFilePathName As String, MailTo As String)
'objTextStream.WriteLine (“Called Email Function.” & vbCrLf)
'Please enter sender’s e-mail address.

'Please enter receiver’s e-mail address.
'Use semicolon between names in the receiver list.

'Please enter e-mail message.
'Body = “The Process has Failed.”
'Subject = “Error Reported in Process”

Set OlkApp = CreateObject(“Outlook.Application”)
Set NewMail = OlkApp.CreateItem(olMailItem)
Set SafeMail = CreateObject(“Redemption.SafeMailItem”) ‘’
Set SafeMail.Item = NewMail ‘’

Set Attachments = NewMail.Attachments
'PromptDialog.Hide
Attachments.Add (LogFilePathName)
With SafeMail
.To = MailTo
.Body = (Body & vbCrLf & vbCrLf)
.Subject = Subject
.Importance = 1
.Send
End With

Set OlkApp = Nothing

End Sub


045331 (BOB member since 2004-01-23)

I am new to VB…so please spare me for askign absic questions…

Please tell me what is the right way to place vb macro in BO report, when you are looking for afterrefreah.

And how can I use procedures. Finally I am planning to put this code on BCA.


045331 (BOB member since 2004-01-23)

I removed public from this code and its working… What is the cause…

I can’t understand this… :frowning:

Public Sub EmailSend(Body As String, Subject As String, LogFilePathName As String, MailTo As String)


045331 (BOB member since 2004-01-23)

I refreshed the report again… and same issue… it refreshed the report properly…kicked off the macro… but looks like not able to call first Email send procedure… it hang…

Any suggestion


045331 (BOB member since 2004-01-23)

Try a search on BOB – There’s a difference between refreshing from the data manager and refreshing from the query panel – maybe that’s the problem…


JennFisher :us: (BOB member since 2002-06-25)

Hi,

I am not able to make it out. previously the same code was runnignf ine. But since i have addedd the call Emailsend… it is behaving like this…


045331 (BOB member since 2004-01-23)

Then maybe your problem is with the the Emailsend procedure…


JennFisher :us: (BOB member since 2002-06-25)

When I go with F8… it runs fine…But when click on refresh at report level… it is hanging…

Any clue


045331 (BOB member since 2004-01-23)

Can anybody help me…

My macro (have one main sub… and one Calling sub)… is runnign fine when go to Tools —> Macro —> vbeditor and press f8 or f5.

But when I click on Data ----> Refresh data in report, it is refreshign the report and kicking off the main macro…but when main macro is calling sub procedure/function…nothign is happening… It will keep running…

Please help me out here… I am new to VB and not able to understand the problem… Its was runnign fine till I was not having this sub procedure in macro.


045331 (BOB member since 2004-01-23)

Hi all,

surprising thing… When I will keep the macro open in vb editor and click on Data–> Data refresh… it is working fine…

What i am doing wrong… I am not able to understand…


045331 (BOB member since 2004-01-23)