Excel updating from XML file - file path specific?

[Guest]

I'm having a littl edifficulty getting Excel to dynamically update from an
XML file. The XML file is generated from a program and I would like the Excel
file to dynamically update.
Problem is that the location of the XML and Excel file can change, that is
they can be anywhere on a hard drive but both are in the same directory. The
mapping of the XML file in Excel appears to be a complete file path
C:/data/report/.... but the location could change. They could be in
C:/data/report/run1 or run2, I don't want to have to re-map each time a
report is generated.

Ideally I would like to remove the directory specific path and use a
directory relative path. This way the XML file and XLS file can exist in any
directory and still update correctly.

Ofcourse, both files will be in the same directory when they are moved.

So my question is, can we change the path to teh XML file?

I'm using Excel 2003 (11.5612.5606) Office Pro.

 

[Norman Jones]

Hi Sean,

Try using:

ThisWorkbook.Path & Application.PathSeparator & "YourFileName"

 

[Guest]

Hi Norman,

Thanks for the reply, but will have to excuse my igorence.

Where, what and how?

Thanks in advance

Sean.

 

[Norman Jones]

Hi Sean,

How are you currently updating?

 

[Guest]

Hi Norman,

Currently using XML maps through the Data >> XML >> XML Source or Import
functions.

This gives me columns for each XML "field". Bit like importing a .CSV file I
guess.

If I move both my .XML and .XLS file to a new location (remove from old) and
try to Data >> XML >> Refresh XML Data I get and error saying cant find
source or something like that.

Cheers,

Sean.