I have a template excel file that is loaded with macros. When the file is opened, a userform opens and the user makes several different selections. Based on the selections, the excel file is populated with information and pictures. I want to make sure that the user does not accidentally save over the template file when he/she is finished with the document.
My original solution was: when the user opened the excel file, it would automatically save under a different name in a temporary location. This would stop the user from being able to overwrite the source file. The users were not happy because the excel file is very large and takes a long time to save.
on another method to stop the user from overwriting the source file? using code? or another method?
how do i go about saving an Excel sheet (template) automatically, using data stored in the A1 cell of Sheet1 as the file name. i want to use an Excel template to capture data, and then store the data in a central location. to do this i am thinking of getting the template to automatically save to a set location e.g. C/: files. is there a simple way to do this.
I already have a macro that on opening the workbook checks to see if the workbook has been renamed outside excel in windows and if so closes the workbook. I need a macro to intercept the save as command and prevent the user from saving the workbook as another name. I would however still like the user to be able to save. I would also like them to be able to save the workbook in another directory but only with the same workbook name.
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons. Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later): Template file name: template_order.xlsm Template file location: \servershared emplate
Total list file name: total_list.xlsx Total list file location: \servershared otal
The template file exists of a "general order info area" A1:N10 The column heading for order data is located at A11:N11 The selection to be copied is A12:N550 - But only rows where column A includes data (not empty). (If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
I work for a company that has about 650 locations. Each location has a location manager that is responsible for submitting a Performance Review spreadsheet for all the employees at their location.
Currently I have an Excel file that has all employees/locations. One of the columns on my spreadsheet is "Location ID"...which is literately a number we've assigned to our Locations. I have an Excel template saved that I'd like each Location to open into.
Can someone help me? I need each location to be it's own spreadesheet...which I have a template created already. I'd prefer to find an automated way to do this...rather than manually creating 650 spreadsheets.
I'd prefer to do this in anoter application, such as MS Access, but senior management already made the decision this will be done in Excel...so I'm stuck with what I got.
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
I am trying to save a chart as a template but when i apply it to another chart it doesn;t have all the original formatting i did. I doesn't retain the gradient fills on the series, the fact that i've changed one series to line on a col chart??
I have some code and then ChDir "W:MET Logbook" ActiveWorkbook.SaveAs Filename:= _ "Micro Evaluation " _ & Format(Date, "(mm-dd) ") & Format(Time, "hh.mm.ss AM/PM") & ".xls", _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False
End Sub And I want this to save my workbook in the different location as a template. Only it saves the workbook as a regular excel workbook instead. Any help with what I can change to save it as a template?
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
I tried to use prtScrn button on the keyboard to capture the screen (excel UI) and then pasted it to excel then saved it. I tried to do the same thing on my friends machine and what surprises me is that the file size of two excel is different.
We use the same OS, excel version and the same machine specs.My saved excel file is around 7.91 mb in size while his is just around 235kb. Why is this like this?
Both pasted image have bmp as a format (default for excel when a you use print screen and directly paste it on excel.).
I have a excel 2003 template that loads a csv file and create a graph.What I am trying to do is remove the Workbook_open macro after it has been run so when they save the file it will not save the macro in the new file.
When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
I have an Excel file that's updated monthly. when it does save its around 16mb and can take up to 12 hours to save, and sometimes just doesn't.
I have tried saving as binary, I have made sure exact size of area to be saved is required, I have tried save with no calculations.
Basically the only reason I need to save it is so that another analysis spreadsheet can pull data from it. The file is heavily formatted, charts, vlookup tables etc, none of which is needed when analysis spreadsheet links to it.
I have a macro enabled spreadsheet and I need to save one sheet into a new file that is in xls format. I can do that --- is there anyway to suppress the Compatibility Checker box to make it that much easier?
The new file will not have macros as will just be a data in rows and columns so compatibility. Interesting is the compatibility checker box says there are links to the original spreadsheet, but I can't find them in the connections menu.
This is a code that i have for having a master workbook, and saving it in different folder so my employee cant access it
Sub NextInvoice() Range("J2").Value = Range("J2").Value + 1 Range("E6:E9,H9,J9,B14:K20,H4,B28:K32,B36:B39,D36:D39,F36:F39,B42:K43,B46:K47,B50:K51,B54:K55,B58:K59,B62:K63,B66:K67,B70:K71,B 74:K75,D78:E78").ClearContents End Sub Sub SaveInvWithNewName() Dim NewFN As Variant 'Copy Invoice to a new workbook
I have found a code for mandatory cell, but it creates a second macro and i was not able to link the two.The mandatory field has drop down of employee's and it is located in the cells D78:E78.
I am using excel 2010 on windows 8. I load a file which has been sent to me via e-mail and save it with an upgraded name, I then do a large amount of data input and save the file again, I do this regularly and even test load it at times to check it has saved, finally I save and close the file and then on reloading I discover the version I have is the first one I saved! all the work I have done on it has disappeared! I also sometimes find random .tmp files with non-sensical names in the folder to which I have saved, but I do not have the rights to open these .tmp files.
I am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
I have a worksheet that has a number in cell K5 - the number is generated on "file open" code and is custom formatted as "TN"0000. Thus 1 appears as TN0001, 2 as TN0002 and so on. I am trying to save a copy of the workbook based on the this cells contents i.e. TN0001.xls, TN0002.xls etc. but the files are saved as 1.xls or 2.xls. The code I am using is
I Have a number(30+) of excel files based off of one template. I have since updated the template, thus making the the old ones obsolete. Is there a way to update the older files to use the new template without having to copy and paste the addition's row by row column by column?
None of the additions to the template will change the placement of existing data, nor will it change the data itself. It will however add Values to cells that were previously empty.
I have an excel worksheet which I would like to email to people however I want them to be able to view it i.e. so they can see the content of the worksheet but I do not want them to be able to copy and paste, save the file etc. etc. basically all I would like them to do is view it, if they want to copy and paste or actually save it to their computer they must enter a password.
I placed the code below in the ThisWorkbook section of a Service Request macro. The intention was to block users of saving the input they had just entered into the blank template. However, some continue to do it, despite the code. Obviously it is not as tight as I expected.
Does anyone know a better, tighter method, short of killing the offending user (which I am tempted to do, believe me!)?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim LoginName As String LoginName = Environ("username") If LoginName "chuckdrago" Then If SaveAsUI = True Then Cancel = True MsgBox "Saving this form is not allowed! Print a Copy" Else ThisWorkbook.Saved = True Cancel = True MsgBox "Saving this form is not allowed! Print a Copy" End If End If End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Saved = True End Sub
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
I have been tasked by the company I work for to come up with a simple to use interface that can be used by someone with little understanding of excel to set the autofilters to certain settings.
So here is what I have:
2 sheets (each with the same number of columns but different data which corresponds to good and no-good)
Each column has an autofilter but only two on each sheet need to be set.
One filter controls valve type (of which there are 10) and the other controls running condition (startup and normal)
Now my employer wants something that is basically idiot proof so that someone can simply open up the excel file, input the valve type and running condition, and be rewarded with only the corresponding data and charts. Now the filters are already in place, the charts already exist and the only thing left is the user interface.
I use the vba to open excel file. like workbooks.open filename:=abc.xlsm but I found that if the file is opened by other. Excel still open the file but it will not notify user that it is opened as read only.
Would you add more line to notify user if the file is opened by other?