× Welcome to the fracta.net forum!

Share your coding ideas or ask questions.

Excel VBA and Open Wave ShiftTrack Plus workforce management and rostering software

More
5 years 8 months ago - 5 years 8 months ago #511 by roller
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.

Attachments:
Last edit: 5 years 8 months ago by roller.

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

More
5 years 8 months ago #512 by mozako
hi roller

thanks you so much. i appreciate it

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

Time to create page: 0.150 seconds
Powered by Kunena Forum