Auto_Run ChDir on new workbook from template

[RRinTetons]

I've got some templates that users utilize to make new reports several times a day. The reports should always be saved in the same location, regardless of the current working directory established by the user's options setting.

I created a macro:

Sub Auto_Run()
ChDir "J:\Reports\"
End Sub

As I understood it, that should run when the worksheet is opened, but when I go to Save in the new workbook it's still pointed at the default save directory established in Options.

Running the macro manually doesn't seem to do it, either, so I must have something wrong.

Help?

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

Hi,

Call your macro in the Workbook_Open event.

Open the VB Editor

ctr+R to vies the Project Explorer

Select the ThisWorkbook object

Select the WROKBOOK in the OBJECTS dropdown

Select OPEN in the PROCEDURES dropdown

Skip,

_[red][/red]

 

[RRinTetons]

The macro is running when the new workbook opens from the template, but it's not changing the directory, or at least not so that Save is looking into the directory they want the new file to go in.

Maybe ChDir isn't the right call? Do I need to do something to change the working directory?

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

ChDir does work.

When doing a Save or Open, the specified path is the one.

Are you SURE that it is executing?

Use the Workbook_Open event!

Skip,

_[red][/red]

 

[RRinTetons]

I've got the statement in the Workbook_Open event. I put a msgbox("Hi") in there with it to make certain it runs when the workbook opens, and it does.

If I add some code to save the file under a random name in the directory I've ChDir'd to, then the Save As... dialog shows the new location.

If I just do the ChDir, then it doesn't.

I want to make certain that the file goes in the new location by default, but the user has to name it, so I'm close but no cigar as yet.

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

"If I just do the ChDir, then it doesn't."

What does that mean. remember, I can ONLY see what you have posted.


Here's what I've done.

1. Set the default

Private Sub Workbook_Open()
    ChDir "d:\My Documents\_Download"
End Sub

2. save the workbook

Sub test()
    ThisWorkbook.SaveAs "NEW TEST"
End Sub

3. observe the results. -- NEW TEST.xls saved in d:\My Documents\_Download

FYI: My Default location is d:\My Documents

Skip,

_[red][/red]

 

[RRinTetons]

I have a template named "1830-SnowmakingReportTemplate.xlt". In its ThisWorkbook VBA section I have this macro:

Private Sub Workbook_Open()
MsgBox ("Hi")
ChDir "J:\Snowmaking Reports\"
End Sub

When I double click the template I get a new workbook as expected. I see the message "Hi", so I believe the macro runs. When I select Save... I'm looking into my default directory, not into J:\Snowmaking Reports.

If I change the macro to:

Private Sub Workbook_Open()
ChDir "J:\Snowmaking Reports\"
ActiveWorkbook.SaveAs Filename:="J:\Snowmaking Reports\Book2.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Then I end up with a new workbook with the name Book2.xls and there's a file named "Book2.xls" in J:\Snowmaking Reports, and if I go to Save as... I am looking into the directory J:\Snowmaking Reports.

What I'm trying to do is get the current working directory changed to J:\Snowmaking Reports without having to actually save a file there so that the user who is creating the file can select Save... to give the new workbook a name, and will be looking into J:\Snowmaking Reports when they do so.

Thanks for knocking this back and forth.

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

The User must do a SaveAs -- NOT a Save.

Skip,

_[red][/red]

 

[RRinTetons]

OK, now it works!

Thanks very much for your assistance.

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

WHAT did you do that works now that did not work before?

This documentation is important for others who may be auditing this thread.

Skip,

_[red][/red]

 

[RRinTetons]

I retyped the code and added:

ActiveSheet.Unprotect

at the top of the macro and

ActiveSheet.Protect

at the bottom.

It seems like the ChDir wasn't working if the sheet was protected. That's a hypothesis, I'd have to test more exhaustively to be certain. But it does work now.

Richard Ray
Jackson Hole Mtn Resort

 

[SkipVought]

Ahhhhh. One of those semingly unrelated environmental issues.

Skip,

_[red][/red]