Can you use a piece of VB code to get the path of the current excel file?

[Smurlos]

Hi all

First off, I hope that this makes sense...

I work for a company which has about 40 branches nationwide.
I created a system which stores customer booking info and emails the customer with confirmation to save users time having to manually type everything - plus its then standard.

As pricing and stuff changes I need to be able to send out an "upgrade" workbook as required which the branches will run and it will automatically run a macro to update the pricing values of the main workbook. The main program workbook is always called HBPOS3.xls. The only thing that will change is the path to this file. It is always stored in HBPOS/HBPOS3.xls. All upgrades are saved into the HBPOS/UpgradeFiles/ folder.

However the path to the HBPOS folder will change dependant on the computer i.e. our's is stored at "C:/Documents And Settings/hbbas/desktop/HBPOS" so it depends on the user i.e. our's is hbbas. I won't know what each path is.

So is there a way that when they open the upgrade file, which will be saved in HBPOS/UpgradeFiles/, that i can use some code to automatically get the PATH to the HBPOS/UpgradeFiles/ directory so that i can then just trim off "/UpgradeFiles/" and replace with "HBPOS3.xls" to transfer the data over?

Is there also a way to check that the file exists? i.e. say it detects the path is C:/HBPOS3/, is there a way to check C:/HBPOS3/HBPOS3.xls exists? and if it doesn't, that it says that it can't find HBPOS3 and so upgrade aborted rather than coming up with a VB error?

Thanks for your time and help. I've not coded anything yet as I'm seeing if it's possible first.

[protonLeah]

Look up DIR function in VB Help

[Smurlos]

You must specify pathname the first time you call the Dir function, or an error occurs. If you also specify file attributes, pathname must be included.

I looked up DIR and it says this.
I already said that when they run the installer, I wouldn't know the path that they had installed HBPOS too - it won't be the same on every computer - so I'd need some way for VB to find HBPOS3.xls's own path and then work from there...

Thanks

[DonkeyOte]

If the file is open - see .FullName and/or .Path (applied to ActiveWorkbook / ThisWorkbook as appropriate)

[Andy Pope]

You can use the .Path property

Please Login or Register  to view this content.

This previous post will should you how to get the users document folder
http://www.excelforum.com/excel-prog...ple-users.html

[snb]

You can easily check one of these standardpaths:

Please Login or Register  to view this content.

checking whether the file exists:

Please Login or Register  to view this content.

[Smurlos]

You can use the .Path property

Please Login or Register  to view this content.

This previous post will should you how to get the users document folder
http://www.excelforum.com/excel-prog...ple-users.html

This works absolutely fine :-)

The path that the install files is held in is always "/UpgradeFiles" which is 13 characters long including the /. I've used left and right before to get the characters from the end of the string or the beginning, is there a way that I can get all the characters in the string except for those last 13 characters, bearing in mind the path will not always be the same dependant on the user?

Thanks a lot for your help

[DonkeyOte]

Assuming the obvious perhaps:

Please Login or Register  to view this content.

[Smurlos]

checking whether the file exists:

Please Login or Register  to view this content.

That also works great - thanks :-)

[Smurlos]

Assuming the obvious perhaps:

Please Login or Register  to view this content.

Magic! thanks :-)

And one last thing. I've searched for this but it doesn't seem to be bringing up any relevant results.

HBPOS3 contains a userform called Dashboard which is the main screen that opens when you first open HBPOS3.

What happens is the administrator clicks on "Run Upgrades" on the dashboard and another userform appears called AdminUpgrade. This is where you select an upgrade file and it opens that upgrade file.

So for example, the one I've just trialed is called "HBPOS_3Jan11.xls". I've managed to make the macro in HBPOS_3Jan11.xls update data in HBPOS3.xls fine. It then says a message that the install is complete and closes HBPOS_3Jan11.xls.

However in order to open HBPOS_3Jan11.xls I had to make the macro in HBPOS3.xls, unload Dashboard and AdminUpgrade. So when HBPOS_3Jan11.xls closes, it leaves HBPOS3.xls open with just its main sheets. Is there anyway that when HBPOS_3Jan11.xls closes, I can make it load up the Dashboard userform from HBPOS3.xls again?

Thank you

[Smurlos]

Please Login or Register  to view this content.

I've tried this. Which works. However it doesn't close the HBPOS_3Jan11.xls file as Dashboard userform is shown.

However if I switch the coding, like so

Please Login or Register  to view this content.

The workbook HBPOS_3Jan11.xls closes and then it doesn't run the macro below.

Is there a way I can close HBPOS_3Jan11.xls and run the LoadDashboard module from HBPOS3.xls?