Save location

[andrew299]

is there anyway of saving to a particular location everytime? - This location will have to be specified by the user the first time that they run it. I have the saveas dialog box but apparently this has too many button clicks. Any way to bring up the same folder everytime?
Thanks
andrew299

 

[VBAjedi]

andrew299,

You don't say specifically, but I'm assuming you are using Excel. You will have to decide where you want to store the filepath. You can write it directly into the code, but then the user can't change it. It's probably easiest to just write the filename to a cell. Use the following line of code to get the filename from the user initially:

NewFileName = Application.GetSaveAsFilename _ 
(, "Microsoft Excel Workbook (*.xls),*.xls")

This brings up the "Save As" dialog, but returns control to your code after getting the path the user wants to save to. Save the NewFileName path wherever you decide to. Now save the workbook using:

ActiveWorkbook.SaveAs FileName:=NewFileName, _ 
FileFormat:=xlWorkbookNormal

If the file already exists, the user will still be prompted with the "Overwrite existing file" popup.

Hope that helps!

VBAjedi

 

[RobBroekhuis]

Programmatically, or whenever the user chooses "Save as" from the menu?
The Save As dialog uses the "current folder". This is usually well-defined and can be set using ChDir and ChDrive, but gets more troublesome when using network locations without drive mapping. Tell us a little more about your needs, and we'll help you along.
Rob

 

[andrew299]

OK thanks for your help, what it is is that this macro will be distributed to lots of people all with there own folder names etc. What I want is to allow the user to select the default folder the first time that the program is run - possibly this could be achieved using the getas filename command that VBAjedi has provided - but not again after this.
I suspect that this is where your code could come into play Rob. I know how to run things once only that is not a problem. I will continue to play with your suggestions and report back if I have any luck.
Thanks again
Andrew299

 

[andrew299]

OK change of plan - simplified
I have the file location from a cell in the worksheet.(C:\AAA\BBB\CCC\Myfile.xls
How would i now extract the directory that its in
c:\AAA\BBB\CCC
I could then set this using ChDir
Andrew299

 

[andrew299]

I assume its using the path commmand but not sure how

 

[andrew299]

Ok this worked for me
h$ is the filename string

Dim file1, file2, loc1
Set file1 = CreateObject(h$)
Set file1 = file2
loc1 = UCase(file2.Path)

ChDir loc1

Thanks for your help everyone
andrew299

 

[Loomah]

hi andrew
not 100% sure what your workaround is going to be so, if the workbook "Myfile.xls" is open you can get the path using

workbooks("Myfile.xls").path

to extract the path from the text you have in the cell using the example above and assuming the cell to be A1 try this

ChDir (Left(Range("A1"), InStrRev([a1], "\") - 1))

;-) If a man says something and there are no women there to hear him, is he still wrong?