Re: Time in VBA for Excel

Dan Wareham Tue, 15 Feb 2005 02:22:10 -0800

Hey Dan,

Thanks for the post and the code ideas. Unfortuantely I still can't get the thing to work even when trying the CONCAT and CAST functions.

As per your request, here is the details of what I have got so far:

I'm running MySQL 4.1.9 with MyODBC 3.51. The table in question is the Users table which has been setup as follows

USERS
=================
UserID INT(11) NOT NULL AUTO_INCREMENT,
CompanyCode TEXT NOT NULL,
AssessmentCode TEXT NOT NULL,
UserCode TEXT NOT NULL,
Profession INT(5) NOT NULL DEFAULT '0',
Category INT(5) NOT NULL DEFAULT '0',
Username VARCHAR(10),
Password VARCHAR(10),
AssessmentDate DATE,
StartTime TIME,
EndTime TIME,
Completion ENUM('Y','N') NOT NULL DEFAULT 'N',

The sample data I'm working with is:

UserID  ::  1
CompanyCode  ::  BEC-24ECMQP-TFXCFDOY-290105
AssessmentCode  ::  BEA-24ECMQP-O7000001-290105
UserCode  ::  BEU-24ECMQP-XR000001-290105
Profession  ::  12
Category  ::  2
Username  ::  BEU11111
Password  ::  password
AssessmentDate  ::  2005-03-05
StartTime  ::  12:33:59
EndTime  ::  14:33:24
Completion  ::  Y

Here is my VBA Code that aims to get the data from the database and insert it into a worksheet. I'm pretty new to VBA coding so please excuse any bad form....

==========

Sub UserData()
   Dim calcMode, updateMode
   Dim ws As Worksheet
   Dim conn As ADODB.Connection
   Dim rec As New ADODB.Recordset
   Dim qtTarget$, sqlQuery$, strAssCode$, strCoCode$
   Dim i&, intUsrCount%

   calcMode = Application.Calculation
   updateMode = Application.ScreenUpdating
   Application.Calculation = xlCalculationManual
   Application.ScreenUpdating = False

   strCoCode = ThisWorkbook.Worksheets("AssmntInfo").Range("C8").Value
   strAssCode = ThisWorkbook.Worksheets("AssmntInfo").Range("M8").Value

   Set ws = ThisWorkbook.Worksheets("UserInfo")
   qtTarget = ws.Names("UsrList").RefersToLocal
   Set conn = OpenDatabase
   If conn Is Nothing Then Exit Sub

sqlQuery = "SELECT ud.UserCode, ct.CategoryDesc, ud.StartTime, ud.EndTime, TIMEDIFF(ud.EndTime,ud.StartTime) AS TotalTime " & _ "FROM Users AS ud " & _ "RIGHT JOIN Categories AS ct ON ud.Category = ct.CategoryID " & _ "WHERE ud.AssessmentCode = '" & strAssCode & "' AND ud.CompanyCode = '" & strCoCode & "'" & _ "ORDER BY ud.UserID ASC"

   rec.Open sqlQuery, conn

   With ws
       .Range(qtTarget).ClearContents
       i = 7
       intUsrCount = 1
       While Not rec.EOF
           .Cells(i, 2) = intUsrCount
           .Cells(i, 3) = rec!UserCode
           .Cells(i, 4) = rec!CategoryDesc
           .Cells(i, 5) = rec!StartTime
           .Cells(i, 6) = rec!EndTime
           .Cells(i, 7) = rec!TotalTime
           i = i + 1
           intUsrCount = intUsrCount + 1
           rec.MoveNext
       Wend

       intUsrCount = intUsrCount - 1
       .Range("J6") = intUsrCount

       .Activate
       .Cells(7, 2).CurrentRegion.Select
       .Names.Add Name:="UsrList", RefersTo:="=" + Selection.Address

   End With

   ThisWorkbook.Worksheets("AssmntInfo").Cells(18, 13) = intUsrCount

   rec.Close
   conn.Close

   Application.Calculation = calcMode
   Application.ScreenUpdating = updateMode
   Application.Calculate

End Sub

Function OpenDatabase() As Connection

   Const DBS$ = "DSN=Assessment;" & _
                "Uid=username;" & _
                "Pwd=password;"

   Dim conn As ADODB.Connection

   On Error Resume Next

   Set conn = New ADODB.Connection

   With conn
       .CursorLocation = adUseClient
       .Open DBS
   End With

If Err <> 0 Then MsgBox "REPORT GRAPH GENERATOR ERROR: " & _ vbCrLf & "Could not connect to database. The report graph processing will be stopped." Exit Function End If

   Set OpenDatabase = conn

End Function

==========

Any further ideas or help will be greatly appreciated.
Kind Regards
[EMAIL PROTECTED]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: Time in VBA for Excel

Reply via email to