Ask Question Forum:
C
O
M
P
U
T
E
R
2
8
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Y-position of the mouse cursor
Attachment:===
Extract data from excel file by querytable in VBA Excel 2003
Modifyquery 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
Attachment:data2.xls
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
