software

I have a macro which saves the spreadsheet to a new directory (currently
the directory is specified within the macro). That's OK for me but I
want to distribute the spreadsheet to a wider audience and to make it
easy for them I'd like to include a piece of code which prompts them
for the directory to save to, just the same as when you do a 'Save As'
from the spreadsheet.

As some of the directory trees are quite complex, I can't ask them to
type the directory location.

Any ideas?--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile: www.excelforum.com/member.php...foamp;userid=3452
View this thread: www.excelforum.com/showthread...hreadid=531463With Application.FileDialog(msoFileDialogFolderPicker)
.Show

MsgBox .SelectedItems(1)

End With

Look up FileDialog in the VBA help--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;ANDYGMquot; gt; wrote in
message ...
gt;
gt; I have a macro which saves the spreadsheet to a new directory (currently
gt; the directory is specified within the macro). That's OK for me but I
gt; want to distribute the spreadsheet to a wider audience and to make it
gt; easy for them I'd like to include a piece of code which prompts them
gt; for the directory to save to, just the same as when you do a 'Save As'
gt; from the spreadsheet.
gt;
gt; As some of the directory trees are quite complex, I can't ask them to
gt; type the directory location.
gt;
gt; Any ideas?
gt;
gt;
gt; --
gt; ANDYGM
gt; ------------------------------------------------------------------------
gt; ANDYGM's Profile:
www.excelforum.com/member.php...foamp;userid=3452
gt; View this thread: www.excelforum.com/showthread...hreadid=531463
gt;

Thanks,

I was able to adapt your suggestion to fit what I want to do.

This is how it looks:

Dim Directory As Variant
'*****CHANGE NEW EXCEL SOURCE FILE PATH HERE******
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
Directory = .SelectedItems(1)
strNewSource = Directory amp; quot;\Report Builder v 1.2.xlsquot;
End With
'*****CHANGE NEW EXCEL SOURCE FILE PATH HERE******--
ANDYGM
------------------------------------------------------------------------
ANDYGM's Profile: www.excelforum.com/member.php...foamp;userid=3452
View this thread: www.excelforum.com/showthread...hreadid=531463