Refresh method - how does Excel connect to my external file? 1

[srogers]

Weekly I am replacing old data with new data in an Excel 2000 worksheet. Through this forum I learned about using the refresh method. This is the code I recorded to do it:

Application.Goto Reference:="VacationInfo"
Selection.QueryTable.Refresh BackgroundQuery:=False

(I expected to see a connection to a file path).
The question is: Apparently Excel is using a SQL query (that it created) to connect to my external .txt file (where I get the data from) correct? Am I able to look at that query or edit it? If the file gets moved to another directory I am wondering if Excel will still be able to find it. I don't really understand how Excel found the file because all I did is record the menu choices, Data, and Refresh Data (I didn't navigate to my file).
Thanks,
Sharon

 

[srogers]

I looked all over for this and once I sent the post I found out I should be looking at the "External Data" toolbar. I think this will lead me to editing the location of the file. I still haven't found a way to view the SQL but maybe Excel doesn't give me access to that?
Thanks anyway.

 

[xlbo]

have a look at
Querytable.connection

This should be what holds your SQL

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[srogers]

Querytable.connection
Where do I find that?

I typed it in to VB Help and read about the Connection property. But that sounds like something I type in myself.

Thanks,
Sharon

 

[srogers]

By the way,
I am using the code below to overwrite all of the data on an Excel worksheet (from a .txt file) and the same macro then refreshes/overwrites data in another worksheet (in the same workbook) from another .txt file.

Do you believe this is an acceptable manner in which to do it? The text files are pretty small - if that matters.
(Would it be better if I had a line in there that explicitly shows the path? - and if so I guess that would start out as QueryTable.Connection?)
Thanks-

Here's what I've got:


Sub InsertPensionWelfareData()
'
' This code refreshes the Pension data from a text file.

Application.Goto Reference:="PensionData"
Selection.QueryTable.Refresh BackgroundQuery:=False

Application.CutCopyMode = False
Range("B1&quot.Select

' Code below refreshes the Welfare worksheet from the
'Welfare text file

Application.Goto Reference:="WelfareData"
Selection.QueryTable.Refresh BackgroundQuery:=False

Application.CutCopyMode = False
Range("B1&quot.Select
End Sub

 

[xlbo]

Can't really comment on whether your method is "acceptable" - if it works...it works.

As to the query data - once you have set up a query on a worksheet, a QUERYTABLE object is created in that worksheet. The properties of the querytable object can be referenced by using code like

With activesheet.querytables(1)
msgbox .connection
msgbox .commandtext
end with

the .connection should be where the text file is
the .commandstring should return the query used on the text file in SQL

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[srogers]

Honestly, I didn't realize I had "set up a query" on my worksheet.
Perhaps that's what it is called when you click Data, Get External Data, and then Data properties and choose how your data is refreshed. And then click Refresh Data.
I didn't click New Database Query and create one - so I suppose through the things I've done above one was created for me by Excel.

Thanks for your reply. I will try that code today and see what I get.

Sharon

 

[srogers]

I just created a new worksheet and simple text file to try the code with.

I think I see what happened now.
When I clicked Refresh Data the Import Text File box popped up and I had to choose a file to refresh with.
When I recorded a macro to do this, the path of the file is not shown in the recorded code(I expected that it would be shown).

So I am supposing that Excel has some kind of query in the background that includes that information when I run the macro.

When I included your code, I saw the connection but I got an error on the .commandtext line. It said: This option is unavailable for this type of external database.

Thanks so much for hanging in there with me. That exercise helped me confirm how Excel found my file. Now I have to find out why the refresh data macro makes the "Import Text File" box open every time. Maybe it has something to do with the location of the file. If that had happened before I never would have had the question about how it knew where the file was!! (the macro I'm using at my friend's office just gets the data, the import text file box doesn't open each time).

Thanks,
Sharon

 

[xlbo]

Glad that you have more of a handle on it. You are correct that if you use the "Import Data" function, it is creating a querytable to bring in the text file. When I recorded the import, I got this:
With ActiveSheet.QueryTables.Add(Connection:="TEXT;W:\F&F.txt", Destination _
:=Range("A1&quot)
.Name = "F&F"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

The Querytable.CONNECTION should give you the location of the text file.

As to the Import Text file box opening, I think it is to do with the:
.TextFilePromptOnRefresh
property
If it is set to true, it propmts for the location when the macro is run. When set to false, it uses the default location set in the .CONNECTION property

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[srogers]

I recall getting that code too when I recorded importing the file. And for awhile, that is how I refreshed my data by importing it each time. But I had to name a range to import it to because sometimes the file was smaller and I need the old data to go away. Then I had the problem of Excel creating range_1, range_2, etc.

So I sent in the question of how to deal with that and someone suggested doing a refresh instead of an add. So I tried recording the data refresh and got only this:

Sub RefreshData()
Selection.QueryTable.Refresh BackgroundQuery:=True
End Sub

I think that's what led me to ask if this was a reliable/good way to get my new data. I guess I thought it was too easy - and then I started worrying about not seeing the path as I had in the .add.

I may have to try the refresh on another worksheet where I overwrite the old data with new data from another worksheet(instead of a text file). Right now, to make the old data go away, I replace it all with a worksheet I filled with zeros. I feel very silly doing it that way but I guess that's how it goes when your just learning it.

Oh and duh - thanks - the data range properties were set to prompt for the file name on refresh on the computer here at home.

Thanks so much for helping me learn about this - I'm sure it is something I can use again.

Sharon

 

[xlbo]

As an addendum - if you have a look at the
.RefreshStyle
property - this should give you some options as to how excel treats "redundant" cells
Basically, the query creates a named range of its own and if you set the right refreshstyle, it will get rid of the old data for you (I think it needs to be xlinsertdelete for that)

Al that happens when you do a refresh is that excel calls the refresh method of the querytable (of which there are many properties also)
The refresh method will refresh the query with whatever properties have been set ie the textfilepromptonrefresh / refreshstyle.

What you should do is import the textfile - then run something like

with activesheet.querytables(1)
.textfilepromptonrefresh = false
.refreshstyle = xlinsertdelete
.any other properties you want to set
end with

then all you need is the original code of

Selection.QueryTable.Refresh BackgroundQuery:=True

and it will refresh with the options you have set - you don't need to run the options again as they will be set until you change them

To see what the properties currently are, just use

msgbox activesheet.querytables(1).connection
or whichever property you want to inspect


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[srogers]

I'll experiment with that.
Thanks again -
Sharon