asked on

Extract data from excel file by querytable in VBA Excel 2003

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

rfportilla

Where is your timer code? I don't see any timer code here.

ASKER CERTIFIED SOLUTION

rfportilla

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

duncanb7

ASKER

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

duncanb7

ASKER

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

rfportilla

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.

duncanb7

ASKER

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 ?

rfportilla

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"

rfportilla

Sorry, I meant the sample is in the link above.

rfportilla

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.

duncanb7

ASKER

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" ?

duncanb7

ASKER

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

rfportilla

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.

duncanb7

ASKER

Time to focus on the extraction from data2.xls excel file. Any expert, please help on it

leonstryker

Is there a reason you are using QueryTables as opposed to just displaying the data from a recordset?

duncanb7

ASKER

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

rockiroads

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)

rockiroads

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.

rockiroads

By the way I came to this thread via here https://www.experts-exchange.com/questions/26488616/Please-help-on-the-question.html?cid=1749

rfportilla

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

rfportilla

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.

StellanRosengren

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

rfportilla

@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.

duncanb7

ASKER

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

Rory Archibald

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.

rockiroads

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.

duncanb7

ASKER

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

rockiroads

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

SOLUTION

StellanRosengren

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

duncanb7

ASKER

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

duncanb7

ASKER

Thanks of all of you to reply the email