Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

how to set time format in ADO and query table

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
duncanb7
Asked:
duncanb7
  • 2
1 Solution
 
StellanRosengrenCommented:
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
 
duncanb7Author Commented:
Both suggestion is working fine
,fast and easy

Thanks for  your reply

Duncan
0
 
StellanRosengrenCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now