- Thank you received: 9
Welcome to the fracta.net forum!
Share your coding ideas or ask questions.
Share your coding ideas or ask questions.
Idea Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software
10 years 5 months ago - 10 years 4 months ago #297
by roller
Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software was created by roller
Not many of you would use Open Wave ShiftTrack Plus rostering and workforce palnning software but if you work in a call center and are responsible for rostering or reporting and would like to extract some data directly from ShiftTrack Plus tables to an Excel workbook you will find this article handy.
The version I am using is 5.5.2.0 this is not the latest version but the logic is the same for all versions I would imagine.
ShiftTrack Plus uses a SQL Express Server database to store all its information. The version I am using has two separate databases; one is called shifttrack and the other shifttrack_plus. There are more than one method to get to this data and I will demonstrate one of them in this article.
To get access to this data you will need to set reference from your Excel VBA editor to the following Objects. You do this via the Tool/references menu in the VBA editor:
1. ShiftTrack Data Connection
2. Open Wave Interop Library
3. Microsoft ActiveX Data Objects 2.8 Library
First lets get into the shittrack database and get all the leave applications from the [leave] table and while we are at it we will get the employees names from the personnel table by performing and left join – this is because the leave details table only contains the employee ID and not the full names.
So the code to do this:
Sub getSTLeave()
wb = "23/12/2013" ‘ the week beginning date for the roster we are interested in
ThisWorkbook.Sheets(1).Range("a2:m65536").Cells.ClearContents
Set STC = ComWrapper.SupportObject.OpenSTDatabase
mySQL = "select t1.* , t2.surname, t2.firstname from leave t1 left join personnel t2 on t1.payroll = t2.payroll where [start] between '"
mySQL = mySQL + Format(wb, "yyyy-mm-dd") + "' and '" + Format(CDate(wb) + 6, "yyyy-mm-dd 23:59") + "' order by 1"
Set rs = New ADODB.Recordset
rs.Open mySQL, STC.Db
ThisWorkbook.Sheets(1).Range("a2").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
After running the above subroutine you should see the data on sheet 1 of your workbook.
It is that simple, and you can use any SQL statement to get data from any other tables.
To get data from the other database shifttrack_plus the macro is almost the same with one different reference. Here how you get the data from the shift_breaks table for example:
Sub getSTP()
Cells.Clear
Set STC = ComWrapper.SupportObject.OpenSTPDatabase ‘ this is the different reference
Set rs = New ADODB.Recordset
rs.Open "select top 100 * from shift_breaks", STC.Db
Range("a1").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
That simple few lines of code and you can automate the extraction of your data not to mention having access to all possible information stored in the software.
The version I am using is 5.5.2.0 this is not the latest version but the logic is the same for all versions I would imagine.
ShiftTrack Plus uses a SQL Express Server database to store all its information. The version I am using has two separate databases; one is called shifttrack and the other shifttrack_plus. There are more than one method to get to this data and I will demonstrate one of them in this article.
To get access to this data you will need to set reference from your Excel VBA editor to the following Objects. You do this via the Tool/references menu in the VBA editor:
1. ShiftTrack Data Connection
2. Open Wave Interop Library
3. Microsoft ActiveX Data Objects 2.8 Library
First lets get into the shittrack database and get all the leave applications from the [leave] table and while we are at it we will get the employees names from the personnel table by performing and left join – this is because the leave details table only contains the employee ID and not the full names.
So the code to do this:
Sub getSTLeave()
wb = "23/12/2013" ‘ the week beginning date for the roster we are interested in
ThisWorkbook.Sheets(1).Range("a2:m65536").Cells.ClearContents
Set STC = ComWrapper.SupportObject.OpenSTDatabase
mySQL = "select t1.* , t2.surname, t2.firstname from leave t1 left join personnel t2 on t1.payroll = t2.payroll where [start] between '"
mySQL = mySQL + Format(wb, "yyyy-mm-dd") + "' and '" + Format(CDate(wb) + 6, "yyyy-mm-dd 23:59") + "' order by 1"
Set rs = New ADODB.Recordset
rs.Open mySQL, STC.Db
ThisWorkbook.Sheets(1).Range("a2").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
After running the above subroutine you should see the data on sheet 1 of your workbook.
It is that simple, and you can use any SQL statement to get data from any other tables.
To get data from the other database shifttrack_plus the macro is almost the same with one different reference. Here how you get the data from the shift_breaks table for example:
Sub getSTP()
Cells.Clear
Set STC = ComWrapper.SupportObject.OpenSTPDatabase ‘ this is the different reference
Set rs = New ADODB.Recordset
rs.Open "select top 100 * from shift_breaks", STC.Db
Range("a1").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
That simple few lines of code and you can automate the extraction of your data not to mention having access to all possible information stored in the software.
Last edit: 10 years 4 months ago by roller.
Please Log in or Create an account to join the conversation.
- mahmod
- Visitor
10 years 4 months ago #310
by mahmod
Replied by mahmod on topic Excel VBA and OPen Wave ShiftTrack Plus workforce management and rostering software
Hi Roller,
I'm using Shift Track Plus version 5.5.0.0
i have difficulties to extract 1 by 1 since my Workforce Team create so many Strand for different department. Is there any way you may assist me?
I'm using Shift Track Plus version 5.5.0.0
i have difficulties to extract 1 by 1 since my Workforce Team create so many Strand for different department. Is there any way you may assist me?
Please Log in or Create an account to join the conversation.
- mahmod
- Visitor
10 years 4 months ago #311
by mahmod
Replied by mahmod on topic Excel VBA and OPen Wave ShiftTrack Plus workforce management and rostering software
Please Log in or Create an account to join the conversation.
10 years 4 months ago #313
by roller
Replied by roller on topic Excel VBA and OPen Wave ShiftTrack Plus workforce management and rostering software
You have to do an SQL statement with joins to collect data from two different tables. The [ShiftTrack].[dbo].[roster] table contains the 'location' and 'strand' columns.
The [ShiftTrack].[dbo].[roster_staff] table has the staff payroll ID and other information.
the join will be on [ShiftTrack].[dbo].[roster_staff].[roster_key] = [ShiftTrack].[dbo].[roster].[key]
The [ShiftTrack].[dbo].[roster_staff] table has the staff payroll ID and other information.
the join will be on [ShiftTrack].[dbo].[roster_staff].[roster_key] = [ShiftTrack].[dbo].[roster].[key]
Please Log in or Create an account to join the conversation.
- mahmod
- Visitor
10 years 4 months ago #317
by mahmod
Replied by mahmod on topic Excel VBA and OPen Wave ShiftTrack Plus workforce management and rostering software
Sorry as I'm not familiar with MySQL Statement. I hope you may assist me to build the script for me:
1. Login ST Plus
2. From Menu, select Schedule --> Quick Roster Editor
3. On the Editor menu, File --> Open a roster
4. Select Location, Strand & Roster Start, then click OK
5. After roster loading, File --> Export Roster
6. Set Date range to export & location the file will be export then click OK.
1. Login ST Plus
2. From Menu, select Schedule --> Quick Roster Editor
3. On the Editor menu, File --> Open a roster
4. Select Location, Strand & Roster Start, then click OK
5. After roster loading, File --> Export Roster
6. Set Date range to export & location the file will be export then click OK.
Please Log in or Create an account to join the conversation.
- mozako
- Visitor
9 years 3 months ago #504
by mozako
Replied by mozako on topic Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software
Hi Edgar
Any sample you have or assist me.i follow you guideline but nothing
Any sample you have or assist me.i follow you guideline but nothing
Please Log in or Create an account to join the conversation.
Time to create page: 0.470 seconds