Best way to refresh data from an external file
 

What I'm doing:
Replacing all the data in a worksheet with new data from
a .txt file.

The code I'm using (I'm refreshing two worksheets here):
Sub RefreshPensionWelfareData()
' Refresh Pension worksheet with the Pension .txt file.

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

Application.CutCopyMode = False
Range("B1").Select

' Refresh the Welfare sheet with the Welfare.txt file.

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

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

The question:
This is working but I am new to VBA and do not understand
how Excel knew which file is which or where it is without
showing the path to the file in any of the code above.
Is the code above an acceptable way of refreshing the
data? And is there a place in Excel where I can go to see
the SQL or where the path to the file is stored? I see
that if the path is changed I can go to Data, Get External
Data and Edit Text Import to navigate to the file.
Thanks,
Sharon

Best way to refresh data from an external file
 

Maybe I can answer my own question.
Who knows if its the best way but it works.
and when I clicked Refresh Data I had to navigate to the
file I wanted to use. If you record a macro Excel does not
include the path to that file in the code. Perhaps that is
because you might move the file and then Excel could not
change your macro so it just keeps the info in some kind
of background query. Just guessing.
Thanks anyway -
Sharon
-----Original Message-----
What I'm doing:
Replacing all the data in a worksheet with new data from
a .txt file.

The code I'm using (I'm refreshing two worksheets here):
Sub RefreshPensionWelfareData()
' Refresh Pension worksheet with the Pension .txt file.

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

Application.CutCopyMode = False
Range("B1").Select

' Refresh the Welfare sheet with the Welfare.txt file.

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

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

The question:
This is working but I am new to VBA and do not understand
how Excel knew which file is which or where it is without
showing the path to the file in any of the code above.
Is the code above an acceptable way of refreshing the
data? And is there a place in Excel where I can go to see
the SQL or where the path to the file is stored? I see
that if the path is changed I can go to Data, Get
External
Data and Edit Text Import to navigate to the file.
Thanks,
Sharon
.

Best way to refresh data from an external file
 

Sharon

When I record a macro it does record the path - I'm not sure why yours is
different. The file and path are stored in two places actually. The
Connection property of the QueryTable object stores the DSN connections
string which will contain the path and the CommandText property (SQL string)
also contains the path. If you go to the immediate window and type

?Sheet1.QueryTables(1).Connection
?Sheet1.QueryTables(1).CommandText

you can see what it is storing for that querytable. As these are strings,
you can also change them before you refresh.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Sharon" wrote in message
...
Maybe I can answer my own question.
Who knows if its the best way but it works.
and when I clicked Refresh Data I had to navigate to the
file I wanted to use. If you record a macro Excel does not
include the path to that file in the code. Perhaps that is
because you might move the file and then Excel could not
change your macro so it just keeps the info in some kind
of background query. Just guessing.
Thanks anyway -
Sharon
-----Original Message-----
What I'm doing:
Replacing all the data in a worksheet with new data from
a .txt file.

The code I'm using (I'm refreshing two worksheets here):
Sub RefreshPensionWelfareData()
' Refresh Pension worksheet with the Pension .txt file.

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

Application.CutCopyMode = False
Range("B1").Select

' Refresh the Welfare sheet with the Welfare.txt file.

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

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

The question:
This is working but I am new to VBA and do not understand
how Excel knew which file is which or where it is without
showing the path to the file in any of the code above.
Is the code above an acceptable way of refreshing the
data? And is there a place in Excel where I can go to see
the SQL or where the path to the file is stored? I see
that if the path is changed I can go to Data, Get
External
Data and Edit Text Import to navigate to the file.
Thanks,
Sharon
.