Open Excel, make copy and switch user to copy?? 2

[NorthNone]

Does anyone know a way to open Excel, make a copy under a different name on the user's personal desktop (not all users) and switch the user to that copy
AND
if the file already exists, prompt them for a new filename?
TIA
NorthNone

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[Chance1234]

Would a template do the trick ?



Chance,

F, G + _HH

 

[SkipVought]

Hi,

"... open Excel..." or open a specific Workbook?

In the specific workbook, Workbook_Open event, just perform a SaveAs to the user's desktop.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[NorthNone]

Chance:
Haven't much experience with templates - I've just been using a master workbook. Could you elaborate or point me to resources on this?

Skip:
Sorry for being imprecise --->"... open Excel..." or open a specific Workbook?"
s/b "Open a specific Workbook"

SaveAs - they will be using this multiple times for different departments - how can I give them the option to use a different file name?

P.S. Skip - I can't wait to 'share' the Tooth company story with family and friends :-#


"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[PHV]

Save your master workbook as a template (*.XLT).
Now double_click on it in the explorer window and discover what happen.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NorthNone]

WAY COOL, PHV!!!!
Have a star!

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[cresbydotcom]

this works for me

Workbooks.Open Filename:="C:\Dell\TimeSheets\Activity_Log_Master.xls"
Workbooks("Activity_Log_Master.xls").Activate
Workbooks("Activity_Log_Master.xls").Sheets("Martin").Select
   'C30 contains text with date in it can be from any source
fn = Workbooks("Activity_Log_Master.xls").Sheets("Martin").Range("C30").Text
ChDir "C:\Dell\TimeSheets"
ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

 

[cresbydotcom]

fn=fname - sorry

 

[NorthNone]

cresbydotcom -> My users need to make multiple copies of this for different departments. It appears to me that your code would write over their existing copies. Is this correct?
TIA
NorthNone

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[SkipVought]

If you are concerned about overwriting existing files, append Date & Time to the filename.

fname = fname & Format(Now, "yyyymmddhhnnss")

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[NorthNone]

THANKS!!!!!

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[NorthNone]

OK - how do you make it stop this behavior? The user opens the template and Excel saves it under a new name. The user closes the file with the new name. The next time the user opens the file, it saves it with a new name...(lather, rinse, repeat).
TIA
NorthNone

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[SkipVought]

Under what conditions do you want something different and WHAT is the different thing that you want?

2 questions; 2 answers.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[NorthNone]

Under what conditions do you want something different"
After making a copy, that copy will no longer do a SaveAs when opened.
"WHAT is the different thing that you want?"
Only the first worksheet runs the code to trigger a SaveAs



"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[SkipVought]

Put a switch or a counter in your workbook in a hidden sheet.

Set the switch, then test the switch to override the saveas logic.


Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[NorthNone]

Cool. Thanks, Skip !

"Character cannot be developed in ease and quiet. Only through experience of trial and suffering can the soul be strengthened, ambition inspired, and success achieved." - Helen Keller

 

[cresbydotcom]

Appending date info to the name is exactly what I was doing - in cells then in VBA to stop people deleting things.