Saving in VBA to a particular location

[Maury Markowitz]

I have a template file that users open, which automatically runs a
script inside. The script runs a few SQL queries, formats up the
results, and then wants to save it out as a new file. Simple enough,
except...

How do I set the default file location? One would expect the path
would be part of the SaveAs, but no such luck!

And, more subtly, if the path does not exist, what do I do? I expect
most people will be opening this file from a particular machine, but
they *might* open it from a mapped drive on their own machines. I
would like to check to see if the default path exists, and if it
doesn't, default to Documents or similar.

 

[GS]

Maury Markowitz expressed precisely :

I have a template file that users open, which automatically runs a
script inside. The script runs a few SQL queries, formats up the
results, and then wants to save it out as a new file. Simple enough,
except...

How do I set the default file location? One would expect the path
would be part of the SaveAs, but no such luck!

And, more subtly, if the path does not exist, what do I do? I expect
most people will be opening this file from a particular machine, but
they *might* open it from a mapped drive on their own machines. I
would like to check to see if the default path exists, and if it
doesn't, default to Documents or similar.

Check out 'ThisWorkbook.Path'

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Maury Markowitz]

Check out 'ThisWorkbook.Path'

But it may not be the same path as where the user opened it. That's
the use-case I'm trying to address.

Is there a way to compare paths that may be network or shortcut
degenerate to the same place? I know there's a term for this, but I
can't recall it.

 

[GS]

Maury Markowitz wrote on 5/7/2012 :

But it may not be the same path as where the user opened it. That's
the use-case I'm trying to address.

Is there a way to compare paths that may be network or shortcut
degenerate to the same place? I know there's a term for this, but I
can't recall it.

That will ALWAYS be the path to the file that contains the VBA that's
running. So if, as you say, this workbook has code that runs
automatically when it's opened then that code will ref that workbook's
path using the syntax I gave you. It doesn't matter where the workbook
is stored, 'ThisWorkbook.Path' will always return its location.

If it's a network path then it will have a UNC path ("\\server\share")
instead of a local path ("C:\MyFolder"). This will always be relative
to the machine that opened the file. *Notice* the difference in the 1st
2 characters of the paths; network paths start with "\\" while local
paths start with a drive letter and a colon.

Paths returned by the 'GetSaveAsFilename' dialog also return the same
path info. Now maybe you're talking about a user selecting a local
folder that's 'mapped' to a network location. In this case that folder
is actually a 'Namespace' link to a network location and so does not
return a valid path via the 'SaveAs' dialog. Windows uses a redirect to
get to the actual network location and so is why you want the UNC path
when doing SaveAs. IOW, the mapped folder is not the 'Absolute PIDL' of
the network location and that's why you can't use it as a path.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion