Save Directory 2

[tdlynn]

Hi again,

With the help of RobBroekhuis I fixed my last problem concerning the user form.

Another problem I am having is that i have created a part of the worksheet which opens a new sheet, copies some data accross and saves it as "report1202" for example (i.e. report then mmyy) That works fine but I can't work out and i'm not sure what the code is to make it save this in the same folder as the original worksheet.

I've tried curDir but that only puts it into the last folder that I was in. Also.. I'm not sure where in the code to put that piece of code. The code that i already have for that operation is..

ActiveWorkbook.SaveAs Filename:= _
"Report" & Format(Now(), "mmyy&quot, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Any help would be appreciated.

Tom

 

[RobBroekhuis]

The folder information is in the activeworkbook.path property. To save as into the old directory:
activeworkbook.saveas activeworkbook.path & "\" & MyNewName & ".xls"
Rob

 

[tdlynn]

Thanks for the speedy reply.. That is similar to what I had in the first place but it is not saving it to the same directory as the workbook. I.e. it is saving it to c:/ only and not the same directory.

If it was just for my own use.. then I would just put in a standard folder name.. but I am making this for a college project and my dad is going to use it properly so it must be able to be self contained and put onto any computer.

Any other ideas?

Tom

 

[RobBroekhuis]

In your first post, you mentioned creating a new sheet. I think you probably meant you created a new workbook. In that case, activeworkbook.path will not be set - you'll need to get the path from the original workbook. So:

activeworkbook.saveas workbooks("the original one&quot.path & "\" & MyNewName & ".xls"

Rob

 

[Loomah]

I haven't gone into this in too much depth but instead of using activeworkbook, use thisworkbook

ie activeworkbook.saveas thisworkbook.path & "\" & MyNewName & ".xls

The problem is probably that the activeworkbook is the 'new' workbook and as such has no path.

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

 

[RobBroekhuis]

Thisworkbook.path will work if the "old" workbook contains your macro code. If the macros are in yet another workbook (such as personal.xls, or an addin), then you'll have to explicitly refer to the old workbook's path.
Rob

 

[tdlynn]

Thanks a lot guys for the replies... I used Rob's code as it was the first in the list and it is working excellently. Think I've finished the project now.. just got to write it all up :S

Thanks again guys

Tom

 

[Loomah]

Rob
Good point!
Your second post wasn't there at the time I wrote my reply and then AOL decided to crash my system. AGAIN!
If I'd seen it I wouldn't have posted as there wouldn't have been any point!
;-) If a man says something and there are no women there to hear him, is he still wrong?