Save Back Up Copy Online At Each Save
Mar 27, 2008
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
View 9 Replies
ADVERTISEMENT
Aug 11, 2007
I have created a spreadsheet for my friend. As my friend is a little 'scared' of excel I created drop downlists to complete the spreadsheet i have also protected all the cells that have formula in them so that they can not be deleted.
the spreadsheet he opens is prefect, he then updates this spreadsheet and has done something wrong but doesn't realise, he then saves the spreadsheet, it is at this point he wants a back up copy of the perfect spreadsheet, that way if he goes back into the saved copy and realises that something is wrong he can go to the back up copy and start again. This spreadsheet is filled in once a week, that way if he has done something wrong and has to refer to the back up copy he only loses one weeks worth of work.
I did say that he could choose save as instead of just save and re-name the file but again he is worried that he will forget!
Can we create a back up file automatically? If yes, can some please tell me how to?
I have never used macros or anything so if you can do this, can I ask that you also explain to me how to do it.
View 9 Replies
View Related
Jul 2, 2009
I am currently using this code which automatically saves a back up copy to another location on my system.
However, the only way i can get it to work is to assign it to a command button. Is there any way in which this will work once i just click the save button.
View 4 Replies
View Related
Mar 11, 2014
am using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
View 6 Replies
View Related
Dec 3, 2012
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
View 4 Replies
View Related
Aug 21, 2012
What I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
View 2 Replies
View Related
Sep 6, 2012
I'm trying to make a macro check if a file has been saved (ever). If so I want the macro to do a regular save (with already esatablished filename and location) before it proceeds with the rest of the macro. If the file hasnt been saved (if it runs from a new workbook) then I want it to pop up the save.as dialog, so that the user can choose the name and location of the file before the macro continues .
The macro itself is saved in personal.xlsx.
View 1 Replies
View Related
May 10, 2008
I would like to add some icons on the left side of excel open file pane to faciliate my work. Because i need to load some files under the same folder many times a day. Does anybody know how to do that? I've seen people has more icons on the pane before. The defaut setting has only 'History', 'My Documents', 'Favorites', 'Desktop' and ' My nutwork places' on it.
View 2 Replies
View Related
Jun 22, 2014
I have a UserForm in which i have inserted a CommandButton. I also have a Table on a Sheet in Excel that i would like to save as PDF.
When i click the CommandButton on the UserForm i would like the SaveAs screen to come up and have the Table (or Used Range in the Excel Sheet) as the selection to be Saved as .PDF format.
View 3 Replies
View Related
Nov 2, 2009
Each of the worksheets in my model use A1 as a control cell for any errors and inconsistencies. My aim is to disable save and close commands in case A1 is not equal to 0 in any of the worksheets.
The code I currently use for that purpose is as follows.
View 2 Replies
View Related
May 25, 2012
Writing code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
View 1 Replies
View Related
Jul 31, 2009
I have figured out how to save the workbook using a variable to saveas but I would like to create a new folder to place the new workbook into. I have tried a few things a am not having any luck. I recorded a macro and the code it produced is as follows.
View 6 Replies
View Related
Jan 17, 2010
I have a macro that copy one sheet of the Active workbook and sends it via email.
I need to add a code in this sheet so when one opens it from the email, with a command button to be able to save the file to specific, fixed folder on the local network with it’s original file name.
View 10 Replies
View Related
Sep 5, 2012
I am trying to disable the save and save as function using a macro, i know that this in its self is quite simple but i am making VBA create a new workbook and i want this (nameless) workbook to have the save feature disabled.
View 1 Replies
View Related
Oct 14, 2013
when i try to save data in other sheet. it is save but, if middle name is null in last record. then after i save other record the data is not save in same row.
i also want "sr.no" come automatically form data..
for.ex.
if in sheet2 last "sr.no" is "7" then it automatically display "sr.no" "8" in sheet1
View 8 Replies
View Related
Jan 26, 2007
I have a spreadsheet with 32 worksheet, I want to write a macro to "save the work, save as.. in a specific location and with a specific name and then reseting all data in specific cells.
View 14 Replies
View Related
Oct 5, 2011
I need my spreadsheet to require that the user save it by clicking a macro button, and not the Save or SaveAs options in the file menu. I have found out how to disable saving entirely:
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
If SaveAsUI Then SaveAsUI = False
End Sub
But this of course prevents my macro button from saving too!
Is there any way to allow VBA code to save the spreadsheet while simultaneously preventing the user from saving through the menu?
View 2 Replies
View Related
Oct 10, 2011
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
View 3 Replies
View Related
Nov 16, 2006
I have a file that I have created that the users will use as a master template to create data that I need. Once they have finished, I want the file to define what the filename should be when they click save.
At the same time, I want to disable "Save As..." as some users will just use that and then will use a file name that another macro (that I will use) won't recognise.
View 7 Replies
View Related
Feb 26, 2007
i have a master workbook. it gets used daily as a template and saved as another file name. the file name is generated by 5 cells that each contain different data (date, street, town, state). is it possible when i hit the save or save as button/icon, for a file name to be automatically generated from the cells i have data in??
PostPosted: Wed Oct 15, 2003 5:12 pm Post subject: Re: change default saveas name for new file w/o saving
Okay, so you want to create a workbook via code, prompt the user for a SaveAs name, then save this workbook?
Sub CreateAndSave()
Dim wb As Workbook
Dim SaveFileName As Variant
Set wb = Workbooks.Add
ChDrive "C"
ChDir "C:VBA Code"
SaveFileName = Application.GetSaveAsFilename("It is a new file.xls", _
"Microsoft Excel Workbook (*.xls),*.xls")
On Error Resume Next
If SaveFileName False Then wb.SaveAs FileName:=SaveFileName........................
View 9 Replies
View Related
Apr 5, 2007
I have a update/Save button on a spreadsheet. So when clicked it updates another sheet and saves the workbook.
how can i have it run the macro when they don't click the button and do
File -> Save.
View 9 Replies
View Related
Dec 13, 2007
I use to send reports to my seniors on daily basis.
I want these reports to be with me and they can just open and check the reports, when I send them. If they try to save file through File-Save or File-Save As, they should not get these options at all in the File Menu.
View 9 Replies
View Related
Feb 5, 2007
My spreadsheet has 32 worksheets, I've recorded a macro to do the job "save, save as with specific name", but what I want is, when save as, I wanted the file name increase in one number, and the date in a specific range "H8:J8" the date should be the next saturday.
here is part of the code.
Range("D11:J65").Select
Selection.ClearContents
Range("J44").Select
Sheets("NsFri").Select
ActiveWindow.SmallScroll Down:=-12
Range("A12:A21,D12:I21").Select
on this example I want the file saved as "PrA4W05.xls", being the next week "PrA4W06.xls", and so on.
and on "H8:J8" the next saturday.
View 9 Replies
View Related
Jun 4, 2007
I have 120 sheets in my workbook and I only need 5 of them to save when there are changes, the other 115 are were data is pulled from. Is it possible to tell excel not to look at a sheet when it saves? I'm just after making it save faster.
This is going to be a workbook that " moves" a round a lot, so I want to the 115 sheets in the same workbook.
View 4 Replies
View Related
Feb 27, 2008
I've been playing with writing a VBA script that will copy and display the value of a cell right before saving the document.
I already have a script that saves the savetime to a cell but have been unsuccesful in using any code from that.
What I'm trying to do is to have the VBA copy different cells depending on the value of another cell.
I've been trying with some IF rules but don't really know if I'm doing this right. I'm also not sure if the code should be in "Sheet1" or in "ThisWorkBook" and if it should be a Private Sub - I've pretty much just tried a bit of this and that.
Let me try and sketch out the scenario:
I want to copy the value of a cell into a second cell depending on the value of a third cell - just before the document is saved.
How could that be done with the cells listed below?Copy-to: "A1" (the value of "B?" depending on the value of "C1")
Copy-from: "B1" IF "C1"=1
Copy-from: "B2" IF "C1"=2
Copy-from: "B3" IF "C1"=3
Copy-from: "B4" IF "C1"=4
Another problem I've been having is to change the colour of the font in a given cell if another cell returns a value of, say, 1.
View 9 Replies
View Related
Nov 7, 2006
I have a workbook with one sheet. Some of the information in this sheet do I want do move to a new workbook, whit only one sheet. Then save the sheet as a CSV file.
I’m sure that this is simple, but I am very new in VBA.
So can someone help me with how to write a macro that create a new file whit only one sheet, and than saves it as a CSV file?
View 5 Replies
View Related
Jul 1, 2008
I want a Macro which will copy my workbook and paste it into a new workbook. Then it will automatically save it under the SAME name as the original and the SaveAs Box must show up (for cosmetic fixes). It is important it asks if it wants to save it under the same name. Example: My file is called "Summary_2008" and when I hit the Macro it will do those things and it will ask me if I want to save it in the SAME directory under the name "Summary_2008" (I will only add a number then).
(The macro must know how to read the original name since I have 7 possible filenames - it depends on which company I choose, that is why it is important it is saved under the same name)
Sub Zamenjaj()
Worksheets.Copy
Dim WS As Worksheet
For Each WS In Worksheets
Zadnja_vrstica = WS.UsedRange.Rows.Count
Zadnji_stolpec = WS.UsedRange.Columns.Count
For rwIndex = 1 To Zadnja_vrstica
For colIndex = 1 To Zadnji_stolpec
With WS.Cells(rwIndex, colIndex)
If WS.Cells(rwIndex, colIndex).HasFormula Then..........................
View 3 Replies
View Related
Aug 15, 2014
I am trying to create a macro to run from a form button, within a report, to save a file to a variable file path and name depending on the date value in cell B5.
The format of B5 looks like - 13/08/2014 16:39
The file path has folders for each year in format "yyyy" with each year having sub folders for each month in format "mm".
The file name is just the date only and is formatted "dd.mm.yy" e.g. 13.08.14
I have tried the code below in various permutations but always end up with an error - Method 'SaveAs' of object '_Workbook' failed.
[Code] ......
View 3 Replies
View Related
Sep 22, 2011
I have a Workbook with around 10 worksheets on, each worksheet is a form.
And when one form is completed we have to save a backup. So i want to assign a macro to a button on a worksheet so when clicked it will save a copy of that worksheet ONLY to a destination and not all the other worksheets along with it.
View 9 Replies
View Related
Oct 19, 2011
How to copy the entire worksheet into a new workbook and save the file to a specific directory with the following filename format ("exceptions191011 - ie. the word exceptions followed by today's date in ddmmyy format).
Here is what I've got so far:
Code:
Sub SaveAs()
Dim FName As String
Dim FPath As String
FPath = "G:Exceptions"
FName = "Exceptions" & Format(Date, "ddmmyy") & ".xls"
Sheets("DataSort").Copy
ThisWorkbook.Sheets("Sheet1").SaveAs Filename:=FPath & "" & FName
End Sub
How to add the following condition that if a file already exists with that name then to bring up an error message stating the file already exists? As well as save the new workbook, it appears to close the original workbook - can I stop this?
View 3 Replies
View Related
