Solved

how to set time format in ADO and  query table

Posted on 2010-09-23
3
914 Views
Last Modified: 2012-05-10
Dear Expert,

I would like to know how to set time format from  both methods of extracting data using
querytable and ADOrecord connection. For example, in the extracted database excel xls file, the
field data of time is 3:00 but it is extracted after by the two method that is shown as
1/1/1900 3:00 in the target  excel file. I just know how to convert back  or do time setting
before any query and ADO connection extract. And I don't want to use a way such as  Range("A1:A100).numberformat="hh:mm:ss" since it is not good practise.
Please review those two Sub routines for data extract from querytable and ADo connection
as follows

Please advise & BR

Duncan



Sub testQuery()


Dim sSQL As String
    Dim sConn As String
    Dim oQT As QueryTable
    Dim wsQuery As Worksheet
   
    Set wsQuery = ActiveWorkbook.Worksheets(1)
   
    sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=D:\wkw\Summary\stock\Charter\stockdata\data2.xls;Extended Properties='Excel 8.0';"
   
    sSQL = "SELECT date, time, index, vol FROM [Sheet1$];"
   
    Set oQT = wsQuery.QueryTables.Add(sConn, wsQuery.Range("A1"), sSQL)
   
    With oQT
        .FieldNames = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 1
       
        .Refresh
    End With
 
End Sub

Sub testADO()


GetDataFromClosedWorkbook "D:\wkw\Summary\stock\Charter\stockdata\d1.xls", "A1:B21", Range("A1:B21"), False
GetDataFromClosedWorkbook "D:\wkw\Summary\stock\Charter\stockdata\d1.xls", "MyDataRange", Range("B3"), True
End Sub


Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
    TargetRange As Range, IncludeFieldNames As Boolean)
' requires a reference to the Microsoft ActiveX Data Objects library
' if SourceRange is a range reference:
'   this will return data from the first worksheet in SourceFile
' if SourceRange is a defined name reference:
'   this will return data from any worksheet in SourceFile
' SourceRange must include the range headers
'
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
        "ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    Set TargetCell = TargetRange.Cells(1, 1)
    If IncludeFieldNames Then
        For i = 0 To rs.Fields.Count - 1
            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
        Next i
        Set TargetCell = TargetCell.Offset(1, 0)
    End If
    TargetCell.CopyFromRecordset rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set TargetCell = Nothing
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Sub
InvalidInput:
    MsgBox "The source file or source range is invalid!", _
        vbExclamation, "Get data from closed workbook"
End Sub


0
Comment
Question by:duncanb7
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
StellanRosengren earned 250 total points
ID: 33749546
Hi Duncan,
For the querytable I suggest you use
.RefreshPeriod = 1
.Refresh
.ResultRange.Columns(2).NumberFormat = "hh:mm:ss"

ResultRange property returns the range for the table and since you specify the order of the fields in the SQL statement you can use the corresponding number for the column to format.

In the second case you need to know which column to format in the target range where you put the content of the recordset. Please have a look at the attached example of how to format the used cells of the 3rd column of the table. I have introduced a variable for the target worksheet because the code i always more maintainable when the Range and Cells methods are qualified (which makes code independent of which worksheet is active). I also introduced a variable for the range to format because I think it makes the code more readable.
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _

    TargetRange As Range, IncludeFieldNames As Boolean)

' requires a reference to the Microsoft ActiveX Data Objects library

' if SourceRange is a range reference:

'   this will return data from the first worksheet in SourceFile

' if SourceRange is a defined name reference:

'   this will return data from any worksheet in SourceFile

' SourceRange must include the range headers

'

'New variables

Dim wsTargetSheet As Worksheet

Dim rngCellsToFormat As Range



Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset

Dim dbConnectionString As String

Dim TargetCell As Range, i As Integer

    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _

        "ReadOnly=1;DBQ=" & SourceFile

    Set dbConnection = New ADODB.Connection

    On Error GoTo InvalidInput

    dbConnection.Open dbConnectionString ' open the database connection

    Set rs = dbConnection.Execute("[" & SourceRange & "]")

    Set TargetCell = TargetRange.Cells(1, 1)

    If IncludeFieldNames Then

        For i = 0 To rs.Fields.Count - 1

            TargetCell.Offset(0, i).Formula = rs.Fields(i).Name

        Next i

        Set TargetCell = TargetCell.Offset(1, 0)

    End If

    TargetCell.CopyFromRecordset rs

    

    'Example formatting column 3

    Set wsTargetSheet = TargetCell.Parent

    With wsTargetSheet

        Set rngCellsToFormat = _

            .Range(TargetCell.Offset(0, 2), .Cells(.Rows.Count, TargetCell.Column + 2).End(xlUp))

    End With

    rngCellsToFormat.NumberFormat = "hh:mm:ss"

    

    rs.Close

    dbConnection.Close ' close the database connection

    Set TargetCell = Nothing

    Set rs = Nothing

    Set dbConnection = Nothing

    On Error GoTo 0

    Exit Sub

InvalidInput:

    MsgBox "The source file or source range is invalid!", _

        vbExclamation, "Get data from closed workbook"

End Sub

Open in new window

0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 33752500
Both suggestion is working fine
,fast and easy

Thanks for  your reply

Duncan
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 33762268
Hi Duncan,
I am glad that I could help. Thanks for the points. But please, explain why the answer was graded "B". From the help page on Experts-Exchange:

What's the right grade to give?
Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now