Re: Time in VBA for Excel
Hi Dan, Sorry for causing additional work to getting the test setup, I will take your advice should I need to post these type of details again With regards to your solution, it works perfectly!!! Great thanks for putting the time and effort in to help sort this problem. Much appreciated. I didn't even know you could connect without a DSN in VBA. I'm basically teaching myself VBA from doing tutorials and experimenting. I'll take a look on the net and see how the DSN-less connection is done. Thanks for the heads up. 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
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-O701-290105
UserCode :: BEU-24ECMQP-XR01-290105
Profession :: 12
Category :: 2
Username :: BEU1
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
Dan Wareham wrote: 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 Before I even get to a solution, here are some tips for next time. When you post details of your setup, try to make it easy to reproduce your setup. It took me 15 minutes to get your test case running on my setup. - use mysql's show create table to create a valid 'create table' statement, or ( even better ): - use mysql's mysqldump to dump the contents of the table you want - don't use DSNs - convert it to a DNS-less connection - I don't have a DSN set up for your test case - if your test case involves data in a spreadsheet, either include the spreadsheet, or alter the code to not rely on this data - include ALL table definitions in the query - you didn't mention the Categories table in your post ( apart from in the SQL ) - don't switch between upper and lower case. You mention a 'USERS' table, and then your code refers to 'Users' --- Now as for the solution, I'm getting time-looking values by using the following: select ud.UserCode, cast(ud.StartTime as char) as StartTime, cast(ud.EndTime as char) as EndTime etc In my previous post I think I said to use 'cast(ud.StartTime as varchar(50))', which doesn't work - I wasn't near a MySQL install, but the basic idea is right, you just have to search for 'cast' on the mysql web site to get the correct usage ( varchar isn't one of the options you can cast to ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time in VBA for Excel
Hello, I have a table in my database with two fields set as Time types. The issue I seem to have is when I use an ADO connection in VBA for Excel to select the two fields. Instead of the expected format HH:MM:SS held within my recordset, I get the current date in the format DD/MM/. Then should it reach a record where the time has been set to the default 00:00:00 I get an error Invalid Use of Null I tried outputting the data from the database to a webpage using scripting and I'm returned with the correct data as entered into the database. This is also confirmed when I view the data in the Query Browser. I've gone through every VBA site possible and found no clues so thought there might be a MySQL wizz that knows what is going on Any help or pointers would be greatly appreciated!! Thanks in advance [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
Dan Wareham wrote:
Hello,
I have a table in my database with two fields set as Time types.
The issue I seem to have is when I use an ADO connection in VBA for
Excel to select the two fields.
Instead of the expected format HH:MM:SS held within my recordset, I
get the current date in the format DD/MM/. Then should it reach a
record where the time has been set to the default 00:00:00 I get an
error Invalid Use of Null
I tried outputting the data from the database to a webpage using
scripting and I'm returned with the correct data as entered into the
database. This is also confirmed when I view the data in the Query
Browser.
I've gone through every VBA site possible and found no clues so
thought there might be a MySQL wizz that knows what is going on
Any help or pointers would be greatly appreciated!!
Thanks in advance
[EMAIL PROTECTED]
I'm not sure about Excel, but Access doesn't understand Time. You have
to give it DateTime, in the format: '30-12-1899 HH:MM:SS'. All DateTime
values starting with '30-12-1899' are interpreted as being a Time field.
Pretty strange stuff. Having said that, we have a number of scripts that
pull Time values into Excel without the issue you're experiencing. In
some places, we just chuck a single quote at the start of the Time value:
select concat('\'', SomeTimeColumn) as MyTime
You could also try casting your values as text:
select cast(SomeTimeColumn as varchar(20) as MyTime
... or something like that. Can't remember if that's exactly how cast()
works, but this is the general idea.
Otherwise post your table def / code and I'll test it out.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
