Solved

Extract data from excel file by querytable in VBA Excel 2003

Posted on 2010-09-21
31
1,756 Views
Last Modified: 2012-05-10
Dear Expert,

I have one data file and stored it in Excel 2003 format file and would like to use
query table in VBA to extract the data with every 1 minutes. After I search the method
in the website such as
http://vbadud.blogspot.com/2007/12/query-table-with-excel-as-data-source.html
and include the libray of "Microsoft Active Data Ojects 6.0 Libray"
and "microsoft ActiveX data objects Recordset 6.0 library"in my VBA excel file . The data2.xls data is extracted but it is not refreshed at every 1 minutes. Please help to view the following
VBA extract subroutine code. Everything is fine except the extracted data from  data2.xls is not updated
in my new excel with my VBA code  as
I wish for every 1 minutes. Please help to point out the mistake I made in the code
Be Reminded I am using Excel 2003 in windows Vista

THanks

DUncan



Sub Excel_QueryTable2()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim wkdir As String

Dim qt As QueryTable
wkdir = ActiveWorkbook.Path
wkdir = wkdir & "\data2.xls"
Debug.Print wkdir
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data2.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

With ActiveSheet.QueryTables.Add(Connection:=oRS, Destination:=Range("A1"))
.FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 1
.Refresh BackgroundQuery:=False
End With

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub




data2.xls
0
Comment
Question by:duncanb7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
  • 5
  • +3
31 Comments
 
LVL 9

Expert Comment

by:rfportilla
ID: 33724514
Where is your timer code?  I don't see any timer code here.
0
 
LVL 9

Accepted Solution

by:
rfportilla earned 250 total points
ID: 33724528
Here is an example on how to use the timer in Excel.  You can specify a time interval which triggers an event.  You put your update code in the event and you are good.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33724560
Where is your example code I can't see it

And the other question is that my code is not working or not okay for set 1 minutes refresh
as we used it for webquerytable
With ActiveSheet.QueryTables.Add(Connection:=oRS, Destination:=Range("A1"))
.FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 1
.Refresh BackgroundQuery:=False
End With

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Author Comment

by:duncanb7
ID: 33724850
Dear Sir,

Why  is not working at   .RefreshPeriod = 1
.Refresh BackgroundQuery:=False ? IT should be similar to Webqeurytable


But when I use Excel Menu Data option to import external link excel file  , it is okay to
set any refresh period by right click the pop up menu at the data area  

Duncan
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33725257
I haven't been able to get this to work either.  The documentation seems to be straightforward.  I even tried to do it by recording a macro and that fails to do it correctly, too.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33725306
actually, we could use timer and make a do loop but it is not good because
we want to do other thing besides refresh the data at the same time.

Query refresh is target , Right ?

Anyone could help on these ?
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33725439
The sample for the timer is in the loop above.  

I finally got it to rename the connection.  I have a feeling it is just this control that sucks.  Once I create the control using the QueryTables syntax, I can use Connections to rename it from the default "Connection" to whatever and it works.  The question is how to get the other properties to set correctly.  Btw, I'm using Excel 2007.  I dont' know if this makes a difference.

ActiveSheet.QueryTables.Add(Connection:=oRS, Destination:=Range("A1"))
ActiveWorkbook.Connections("Connection").Name = "myNewQry"
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33725444
Sorry, I meant the sample is in the link above.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33725491
I have an answer, try using Microsoft ActiveX Data Objects 2.8 instead.  It doesn't set the name, but it sets the refresh correctly.  You can always set the name after with the Connections line.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33725593
Dear rfportilla,

it is same as before even change it to 2.8 version.
Do you think the problem is related to "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data2.xls;Extended Properties=Excel 8.0;Persist Security Info=False" ?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33725707

When I try to convert to data2.xls inot data2.txt, and use connection:="TEXT;..
that is working fine and update for every i minutes. But why is hard and not working
to extract data2.xls excel data directly ?
Please see my following code for data2.txt extraction

Sub test()
Dim wkdir As String
wkdir = ActiveWorkbook.Path
wkdir = wkdir & "\data2.txt"
Debug.Print wkdir
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & wkdir, Destination:=Range("A1"))
.FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 1
.Refresh BackgroundQuery:=False
End With
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33726046
Yeah, for some reason it does not like the extraction from the Excel document. I am experiencing the same thing.  I tested with a web page and no problems.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33726091
Time to focus on the extraction from data2.xls excel file. Any expert, please help on it
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 33728976
Is there a reason you are using QueryTables as opposed to just displaying the data from a recordset?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33731396
because during querytable is waiting, I can do other excel editing.
But if using program loop to extract the data, I could not do a kind of mutli-tasking
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33731752
What you need to do is set the repeatable timer in Excel using Application.OnTime

'Define interval (type double) using timeserial (takes hours, mins, secs)
    interval = Now + TimeSerial(0, 1, 0)

'Now tell the timer details of the interval and what procedure to call
    Application.OnTime EarliestTime:=interval, Procedure:="Excel_QueryTable2", _
        Schedule:=True


Please note your procedure you created in your first post needs to be inside a module in order to work (and not in any of the sheet or thisworkbook code)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33731758
rfportilla, going thru the tons of posts here you mentioned some timer code but I couldn't see it. What was it you did? Apologise if you already mentioned Application.OnTime or something similar.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33731764
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33732551
Sorry, I had some stuff I had to get done.  Here is a simple solution.  I just did a quick macro that opens the file, copies the cells, closes the file and pastes.  Then put this on a timer.

Regarding the timer, I thought I posted this:  http://sebthom.de/28-excel-vba-timer/

There is a sample excel document there with the code.  It should be pretty easy to follow.  If not, let me know and I will go into more detail.

@Rockiroads it does use application.ontime
Sub Macro5()
'
' Macro5 Macro
'

'
    Workbooks.Open Filename:="data2.xls"
    Range("A1:D6").Select
    Selection.Copy
    ActiveWindow.Close
    ActiveSheet.Paste
End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33732567
Poster, this was truly a pain in the butt.  I don't know why this control doesn't behave any better.  There is just something screwy with the support for external sources.  I really spent more time on this then I usually would, but I just can't understand why it won't work the way we expect it to.  Oh well.  I hope you have enough to work with.
0
 
LVL 13

Expert Comment

by:StellanRosengren
ID: 33732588
Hi,
I think auto refresh stops because you specify the Connection for the querytable as a Recordset which you close and destroy. If you use the connection string instead I believe it will work. You can use application.ontime but that is a little more tricky.

Regards,
Stellan
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 33732807
@StellanRoseng...  I did try this.  1. The connection string does not specify the recordset so it wouldn't know what to pull  2. The recordset should be in the definition of the connection.   3. It fails with some kind of type error.

I don't know what is going on.
 
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33733543
Dear All,

Finally I used ontime timer function to solve the problem from  .RefreshPeriod = 1 in querytable which is only working for
Webquerytable and text file query table but not for excel file like data2.xls . I don't know why but at least the problem is solved
Please view the code.





Sub Excel_QueryTable2()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim wkdir As String

Call Refresh_query
Call Start_Timer



End Sub

Sub Refresh_query()
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\data2.xls;Extended Properties=Excel 8.0"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select date,time,index,vol from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

With ActiveSheet.QueryTables.Add(Connection:=oRS, Destination:=Range("A1"))

.FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 1
    .Refresh BackgroundQuery:=False
'.Refresh
End With

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
Call Start_Timer

End Sub


Sub Start_Timer()
Application.OnTime Now() + TimeValue("00:01:00"), "Refresh_Query"
End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33734324
As Stellan said, you did not have a querytable linked to a live data source, hence you can't refresh it. If you had connected directly to the workbook rather than using a recordset, you would have been fine.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33735367
rfportilla, I could not see any link in #33724528 or before but thanks for posting it later on.

Duncan, did u understand my post? I know rfportilla spent some time in helping you out and deserves credit but just curious that is all. My suggestion with the code on your initial post will make it work so wondered if you tried it or not.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 33736720
Dear Experts,

Since rfportilla remind mein his reply  to use timer so that I read the website again at
http://vbadud.blogspot.com/2007/12/query-table-with-excel-as-data-source.html
and web search  for the word of "timer in vba" and find out the sub() for timer
from application.ontime, I mean he trigger to do so.

Roya, Stellan and Rockiraod are also trigger me  to solve other problem  for .refreshperiod=1 m and I may try to your suggestion  and look
around  the VBD code for deleting the code of close of ADO record set that  would help.

If use open file of data2.xls, that will take a long time since data2.xls is live data I extract for every 5 seconds  and I will take
6000 data daily.  WHen I use ADO recordset and query method , it will be faster since the live data  I doesn't  need to save it in
data2.xls but the querytable could read the latest data from computer memory for data2.xls before any data saving . THe question is I need to
locate the latest row of data2.xls by settting correct ADO recordset row.

Now I better understanding which method is beter. Anyway, thanks for all of you.

Duncan

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33736839
why did u websearch for timer in vba when I gave you sample Applicaton.OnTime code to try? you thought it might not work? By the way, I didnt mention refreshperiod
0
 
LVL 13

Assisted Solution

by:StellanRosengren
StellanRosengren earned 250 total points
ID: 33739036
Hi
Here is a sample to show how you can connect directly. I have tried it on Excel2003 and it works.

/Stellan
Dim sSQL As String
    Dim sConn As String
    Dim oQT As QueryTable
    Dim wsQuery As Worksheet
    
    Set wsQuery = ThisWorkbook.Worksheets(1)
    
    sConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=F:\Data_for_QueryTable.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

Open in new window

0
 
LVL 13

Author Comment

by:duncanb7
ID: 33741366
Dear rockiraods,

I look at  rfportilla's post to see the timer to remind me so I seach the word of"timer" in web and then find out ontime function at one website  before I see your post. Just tell exactly answer who is first to trigger me to finish the task .Anyway thanks for your reply

Dear StellanRoseng,

You code is correct totally and match my question completely  at the beginning, I want to use querytabel to extract the data with refreshperiod
You should share the bonus and credit with rfportilla

Best Regards

Duncan
0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 33744038
Thanks of all of you to reply the email
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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