Saving EXCEL spread sheet in two locations
Thread poster: Sarah Brenchley

Sarah Brenchley  Identity Verified
Local time: 06:52
Spanish to English
+ ...
Sep 24, 2006

Hi, What I need to do is to save an excel spreadsheet in two different locations whenever I press SAVE button in Excel. I've seen articles about macros that can do this but I don't have much experience of macros so am not sure how to go about creating one, etc.
My final idea is to synchronise the file with my PDA so ideally I would like a simplified version (with only some of the columns visible/available) of the main file saved.
Can anyone help?
Thanks,
Sarah.


Direct link Reply with quote
 
Robert Zawadzki  Identity Verified
Local time: 06:52
English to Polish
+ ...
I can write it... Sep 24, 2006

Only I need to know where to save this copy. Or perhaps add a "Copy of " to a name. If you give me your landline (not mobile) number, I shall call you and discuss things.

[Edited at 2006-09-24 11:13]

[Edited at 2006-09-24 12:24]


Direct link Reply with quote
 
Robert Zawadzki  Identity Verified
Local time: 06:52
English to Polish
+ ...
The macro itself Sep 24, 2006

You only need to insert it in the right place (thisworkbook)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Application.EnableEvents = False

ThisWorkbook.Save

NewName = "Copy of " + ThisWorkbook.Name

ActiveWorkbook.SaveCopyAs NewName

Application.EnableEvents = True

Cancel = True 'cancel original save request

End Sub


Direct link Reply with quote
 

Sarah Brenchley  Identity Verified
Local time: 06:52
Spanish to English
+ ...
TOPIC STARTER
Thanks Robert Sep 24, 2006

I discovered this macro and it gives a blow-by-blow account which is what I needed:

Please try the following

1. Tools - Macro - security - security level - low
Tools - Macro - security - trusted publishers- tick the two boxes at
left-lower corner
2. Tools - macro - Visual Basic Editor
3. Insert module
4. Check the folders names and the filename and
Copy
Sub Savingtwofolders()
ChDir1 = "C:\MyDocuments\"
Chdir2 = "C:\shared\"
filename="file1.xls"
ActiveWorkbook.SaveAs Filename:=chDir1+filename
ActiveWorkbook.SaveAs Filename:=chDir2+filename
End Sub
5. File - Close and return to Excel
6. Select the sheet where you have the part numbers
7. Tools - macro - macros
select Savingtwofolders and press run
or

7. make the excecution easier by inserting a button
View - toolbars
Activate Forms
draw a command button
assign this to savingtwofolders

8. Press the button

or add a new command to the File menu

(Courtesy of Adelaide on expertsabout.com)

My next challenge will be to save a simplifed version rather than the full one.

Thanks for your help though.


Direct link Reply with quote
 
Robert Zawadzki  Identity Verified
Local time: 06:52
English to Polish
+ ...
I'd say my solution is better Sep 24, 2006

When you install it the right way, it simply saves the sheet in two locations whenever you select 'Save', whether from a menu, an icon or even using a macro.

Direct link Reply with quote
 

Sarah Brenchley  Identity Verified
Local time: 06:52
Spanish to English
+ ...
TOPIC STARTER
Yes Sep 24, 2006

Hi Robert,
It probably is better but I really haven't a clue about macros.
Thanks for your help anyway.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Saving EXCEL spread sheet in two locations

Advanced search






CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use SDL Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

More info »
PerfectIt consistency checker
Faster Checking, Greater Accuracy

PerfectIt helps deliver error-free documents. It improves consistency, ensures quality and helps to enforce style guides. It’s a powerful tool for pro users, and comes with the assurance of a 30-day money back guarantee.

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search