Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Question VB Code to run report for a custom timeframe

More
5 years 8 months ago #692 by thesage01
Hi all,

I need to run a customer historical designer report each week and want to do this via VB code.

I've sort or partially automated this so far in that I have to manually run the CMS report and paste the result into excel but then I run a VB macro in excel to tidy up the data and filter for what I want.

I want to be able to run the macro from excel and it would pull the report and paste it in but for a date range I select. It's this bit that I can't get my head around.

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #693 by roller
Very open ended question. You should show what you have written so far and where is the part you are missing and require help with.

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #694 by thesage01
Thanks for your reply. Here's the code I've got so far.

I can now get the correct report to run however it only ever runs the last report that I ran manually via CMS if that makes sense.

I'm trying to figure out how to have the macro run a date range I specify in my worksheet.


Sub Update_PastDate()

Dim cvsApp As Object
Dim cvsConn As Object
Dim cvsSrv As Object
Dim Rep As Object
Dim Info As Object, Log As Object, b As Object

Set cvsApp = CreateObject("ACSUP.cvsApplication")
Set cvsSrv = CreateObject("ACSUPSRV.cvsServer")
Set Rep = CreateObject("ACSREP.cvsReport")

Set cvsSrv = cvsApp.Servers(1)

'Team Summary.

On Error Resume Next
cvsSrv.Reports.ACD = 1
Set Info = cvsSrv.Reports.Reports("Historical\Designer\Handle Time Team Breakdown")


If Info Is Nothing Then
If cvsSrv.Interactive Then
MsgBox "The report Historical\Designer\Agent Group Daily - Staff Summary was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
Else
Set Log = CreateObject("ACSERR.cvsLog")
Log.AutoLogWrite "The report Historical\Designer\Agent Group Daily - Staff Summary was not found on ACD 1."
Set Log = Nothing
End If
Else
b = cvsSrv.Reports.CreateReport(Info, Rep)
If b Then
Debug.Print Rep.SetProperty("Agent Group", "Support Orange")
Debug.Print Rep.SetProperty("Dates", B36)


b = Rep.ExportData("", 9, 0, False, True, False)
Set wk = ThisWorkbook

Sheets("Data").Select
Range("A1: Q13").Select
Selection.ClearContents

wk.Sheets(2).Cells(1, 1).PasteSpecial

Rep.Quit
If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
Set Rep = Nothing
End If
End If

Set Info = Nothing

End Sub

Please Log in or Create an account to join the conversation.

More
5 years 8 months ago #695 by roller
It is possibly not running at all. The reason why you think it runs the last report you did in CMS maybe because that results is still sitting in the paste memory from when you run CMS!

In your code you have debug.print.... where you set the properties, look in the VBA immediate window and check if the debug.print is generating a True or False.

If you see true that's good news, if false it means the code is not running. If you see nothing at all it also means it's not running.

Finally the best way to extract the VBA code you need is to create a script for the report from CMS and save the file on your desktop then use notepad to open that .acsauto file, what you will see is almost all the VBA code needed.

Please Log in or Create an account to join the conversation.

Time to create page: 0.401 seconds
Powered by Kunena Forum