Menu
I have written an Excel VBA macro which imports data from a HTML file (stored locally) before performing calculations on the data.
At the moment the HTML file is referred to with an absolute path:
Workbooks.Open FileName:="C:\Documents and Settings\Senior Caterer\My Documents\Endurance Calculation\TRICATEndurance Summary.html" However I want to use a relative path to refer to it as opposed to absolute (this is because I want to distribute the spreadsheet to colleagues who might not use the same folder structure). As the html file and the excel spreadsheet sit in the same folder I would not have thought this would be difficult, however I am just completely unable to do it. I have searched on the web and the suggested solutions have all appeared very complicated.
I am using Excel 2000 and 2002 at work, but as I plan to distribute it I would want it to work with as many versions of Excel as possible.
Any suggestions gratefully received.
717
Absolute URLs must be used to link to other websites that are not located on the same domain. Relative URLs, on the other hand, are more easy to use because they are relative to the page they are on.
In VBA the path is built using backslash... So, I would suggest to use "\" instead of "/". You might want to check whether mypath contains the text you're expecting... And you probably need to add a folder separator ( / or ` after complete .
The absolutePath function works by beginning at the starting folder and moving up one level for each "../" in the relative path. Then it concatenates the changed starting folder with the relative path to produce the equivalent absolute path.
If you do a lot of testing and move content frequently between domains, then relative links are the best solution. Absolute paths are more secure, protect content, and prevent duplicate pages. The main point is that the link format you prefer should be applied to all URLs on the site.
Just to clarify what yalestar said, this will give you the relative path:
Workbooks.Open FileName:= ThisWorkbook.Path & "\TRICATEndurance Summary.html" If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
