- 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
9 years 1 month ago - 9 years 1 month ago #511
by roller
Replied by roller on topic Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software
I have been very busy but I will give you a start to go by and you have to work out the rest as I will not have time to answer much questions right now.
Start a new VBA project and add references to the following:
Open Wave Interop Library
ShiftTrack Data Connection
Microsoft ActiveX data Objects 2.8
In a module paste the below code:
Dim rs As ADODB.Recordset
Sub getSTLeave()
ThisWorkbook.Sheets("sheet1").Cells.ClearContents
Set STC = ComWrapper.SupportObject.OpenSTDatabase
mySQL = " SELECT * FROM roster r, roster_staff rs WHERE r.[key] = rs.roster_key and r.[start] >= '2015-02-01' and r.strand = 0"
Set rs = New ADODB.Recordset
rs.Open mySQL, STC.Db
ThisWorkbook.Sheets("sheet1").Range("a1").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
If you run the above it will give you all the roster details from 1st of February to current for strand = 0. Now the strands are numbered from 0 and up but the description to what 0, 1, 2 are called are in a different table. You can experiment by changing the strand number in the SQL statement to work out which is which or you can look up the tables in the Shifttrack database.
You really need to explore the database to know where things are store and ShiftTack comes with a basic SQL Tool which is handy to study the structure, see image.
Start a new VBA project and add references to the following:
Open Wave Interop Library
ShiftTrack Data Connection
Microsoft ActiveX data Objects 2.8
In a module paste the below code:
Dim rs As ADODB.Recordset
Sub getSTLeave()
ThisWorkbook.Sheets("sheet1").Cells.ClearContents
Set STC = ComWrapper.SupportObject.OpenSTDatabase
mySQL = " SELECT * FROM roster r, roster_staff rs WHERE r.[key] = rs.roster_key and r.[start] >= '2015-02-01' and r.strand = 0"
Set rs = New ADODB.Recordset
rs.Open mySQL, STC.Db
ThisWorkbook.Sheets("sheet1").Range("a1").CopyFromRecordset rs
rs.Close
STC.CloseDb
Set Db = Nothing
Set cn = Nothing
Set rs = Nothing
End Sub
If you run the above it will give you all the roster details from 1st of February to current for strand = 0. Now the strands are numbered from 0 and up but the description to what 0, 1, 2 are called are in a different table. You can experiment by changing the strand number in the SQL statement to work out which is which or you can look up the tables in the Shifttrack database.
You really need to explore the database to know where things are store and ShiftTack comes with a basic SQL Tool which is handy to study the structure, see image.
Last edit: 9 years 1 month ago by roller.
Please Log in or Create an account to join the conversation.
- mozako
- Visitor
9 years 1 month ago #512
by mozako
Replied by mozako on topic Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software
hi roller
thanks you so much. i appreciate it
thanks you so much. i appreciate it
Please Log in or Create an account to join the conversation.
Time to create page: 0.669 seconds