Solved

how to set time format in ADO and  query table

Posted on 2010-09-23
3
917 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA: copy range dynamically based on config sheet v2 3 35
Excel Cell Total 3 21
Copying from excel I am getting extra text 11 43
ProperCase in Excel (Sheet) 3 13
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

810 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