Solved

Extract data from excel file by querytable in VBA Excel 2003

Posted on 2010-09-21
31
1,718 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
  • 11
  • 10
  • 5
  • +3
31 Comments
 
LVL 9

Expert Comment

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

Accepted Solution

by:
rfportilla earned 250 total points
Comment Utility
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
Comment Utility
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
 
LVL 13

Author Comment

by:duncanb7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, I meant the sample is in the link above.
0
 
LVL 9

Expert Comment

by:rfportilla
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Time to focus on the extraction from data2.xls excel file. Any expert, please help on it
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 9

Expert Comment

by:rfportilla
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks of all of you to reply the email
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

15 Experts available now in Live!

Get 1:1 Help Now