opening other files using VBA

[handlewithcare535]

I am looking to have a command button which, when pressed, opens another excel file. This is simple enough, but I can't work out how to make this work when the file location is different. I.e. I want to be able to email these files to other users and for the code to still work. Is there a way to do this?

Thanks,

hwc535

 

[fumei]

Hmmm, I would suggest you try and rephrase that.

What exactly are you asking?

Is there a way to do this?

To do what exactly?

I can't work out how to make this work when the file location is different.

WHICH file location? The file trying to fire the code, or the one that is trying to be opened?

If the code has a location of a file to be opened...and that file is now in a different location...well, the code won't work.

Are you asking about a file (with code) that does not seem to work at all when another user tries it - then say that. That could be a macro security issue.

Not sure what you are really asking about.

Gerry
My paintings and sculpture

 

[SkipVought]

Hi,

Take a look at the GetOpenFileName method of acquiring the desired workbook name. Then use the selected filename to OPRN the workbook

Skip,

_[red][/red]

 

[handlewithcare535]

Sorry, yes I can see how that would make no sense now!

What I want to do is:

I have a bunch of excel files, all linked by command buttons which open another file when clicked. This works fine. However, I want to email these excel files to other people and for them to be able to use them as well. however, because of the code behind the command button, it includes the file path, and obviously when it is on another computer, this does not work. So my question is:

Is there a way for the links still to work when people are using them on different computers?

Thanks

hwc535

 

[fumei]

Simply...no. How could there be? As you state, the code uses a file path. If the file path is not valid on the other computer...then it is not valid. Period.

That being said, if you wanted to, you certainly could have code that would look for the files. When it finds them, then it opens them.

Gerry
My paintings and sculpture

 

[IknowMe]

Are you using the network path or a mapped drive in the file path?

XXX0000123\folder\file.xls

or

c:\folder\file.xls

post one of your paths (sanitized if you wish)

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[handlewithcare535]

it's the c:\folder\file.xls format

 

[IknowMe]

Can you move the files to a shared drive somewhere? Or set up a shared folder on your PC?

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[firefytr]

As MrWilson points out, do you have the ability to upload the file(s) to a server?

More importantly, the structure here seems a little foggy to me and sounds like it's probably inefficient. Sounds like an addin would do here. This addin could be uploaded to the server as well, or just emailed around. Depending on the scope of how in-depth you wanted to make it could increase the difficulty of it, but I'd opt for it over a bunch of command buttons, IMHO.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fumei]

Well yes, if the files are accessible to the other people - everyone has the same path essentially - of course this can be done. However, if the files are local (your machine), then you are kind of stuck.

Or if you are wanting to email them to other people who are on a completely different network, then you are stuck.

Again, there are ways. Are these files all in the same folder? Will, when sent to others, be put in the same folder (it doesn't matter which one)? Because if they files will be in the SAME folder - regardless of what folder that is, you could have your commandbuttons grab the current path of the file. Then append the filename for the file to be opened.

Your machine:
c:\folder\file.xls grabs path (variable ThisPath = c:\folder\) and opens....

ThisPath otherfile1.xls
ThisPath otherfile2.xls
ThisPath otherfile3.xls
ThisPath otherfile4.xls

Other machine:
c:\someotherfolder\EmailStuff\file.xls grabs path (variable ThisPath = c:\someotherfolder\EmailStuff\) and opens....

ThisPath otherfile1.xls
ThisPath otherfile2.xls
ThisPath otherfile3.xls
ThisPath otherfile4.xls

Gerry
My paintings and sculpture