I have a macro-enabled template file in Excel 2007. I would like the user to be unable to save in any format other than macro-enabled. They should be free to choose a path and filename, but not the file type.
I presume this means some VBA code in the before save event, but I don't know what.
I am using the macro below to save a file. It works with the extension .xlsx but not xlsm. I get a message that I can't use this extension for this file type. I am missing something fundamental.
Using Excel 2010 Sub testsave()' Dim a As String Dim b As String Dim c As String Dim d As String b = "Myyfile" c = b & ".xlsm" a = ThisWorkbook.Path d = a & "" & c ActiveWorkbook.SaveAs Filename:=d End Sub
FYI - there are no macros in the file I am trying to save.
Excel 2010, Win 7. I have attached a .xlsm file to an email. The contained macros run perfectly, and one of them is "public" so it shows up in "Developer - Macros", and "Customize Quick Access Toolbar" lists.
If the recipient clicks and drags the attachment to a folder, or copy/pastes the attachment, it works fine. (Macros are enabled, etc.)
However, if the recipient right clicks on the email attachment, and selects "SAVE AS" from the item list, the file saves, has the right name and extension, looks ok (the icon has the exclamation point, etc), and it is the correct size, but it simply will not load. You can double click, do a file open, etc. but it will not load. It's a hidden workbook, but if you "open it" and Alt-F11 to show the VBA editor, it isn't there!
It's not stopping the project - we simply tell them to click/drag, etc. but I totally do NOT understand why that happens.
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
There are two excel files abc.xlsm and xyz.xlsm enclosed. download these two excel files to your desktop. Then open abc.xlsm and run following code.
After running code above you will see that you have a new file in your desktop named xyz.rar. Everyhing is okey until now.
Now, delete abc.xlsm, xyz.xlsm, xyz.rar from your desktop. Then download enclosed file named klm.xlsm to your desktop. Then open klm.xlsm and run following code.
After running code above why there is no klm.rar file in your desktop? Is it not possible to create a zip file running code includes same workbook? I mean why I cant zip excel file, from same excel file?
Suppose there is an existing ms excel file. There are some data inside, taking 5 columns. Here is what I want to do:
I want the excel file to automatically generate a new column, the 6th, when the file is opened, say double-clicked.I want the 6th column do some summation based on the indexing/flag information int he 4th column, and the values from 5th column. For example: Suppose 4th column has binary entries (1 or 0). If the entry is 1, then pick the entry in the same row in 5th column, and do the summation for all this kind of values (values in 5th column with a 1 flag in 4th column)
I have a folder with multiple text files. I want to import one of these files to a sheet called data in an existing Excel file. The excel file has references to this data sheet on other sheets which I want to keep. I want to save this excel file with the name the text file has and repeat this for all the text files in the folder.
I found a macro a while back which does the job nicely of importing and saving the Excel file, however all the data in the other sheets gets lost once it is saved. How to keep the data?
Sub tgr() Const txtFldrPath As String = "FOLDER WITH TEXT FILES" 'Change to folder path containing text files Const xlsFldrPath As String = "FOLDER FOR EXCEL FILES" 'Change to folder path excel files will be saved to Dim CurrentFile As String: CurrentFile = Dir(txtFldrPath & "" & "*.txt") Dim strLine() As String Dim LineIndex As Long
I'm creating a template xlsm file that has built in marcos. This file will be used by the Engineer as a template, where they fill in the data, and then send it to the team. when the case in finished, they will use clear data macro.
The Problem I want the template to have a macro runs with startup of the file that checks for the lastest template in a network folder (shared folder where they will have read only access). If the used file is the latest one, OK. If not, give a custom msg with the location on the network.
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I would like to create a PDF file from a RANGE Name, excel 2007, attach it to MS Outlook 2007, Once in Outlook I will select the recipient, add some remarks and hit send. I can do this now with a workbook but not a range.
I am working on a project where i am having 8 excel files saved at diffrent location so i want to create userform which will open particular file location and from that user can select the file which he want and then can go further. so i need a coding so that user will be prompt 8 times with file location. for eg. once user select particular file from location then again this code route him to select next file from file location. is it possible??
I tried using below code but in this code when i run userform file is not opening but when i run this code mannually by pressing F8 desired file is opening i dont know what is glitch in this ? another problem i am facing is not able to understand how to repeat this steps again to open another file using this code??
VB: Private Sub Commandbutton1_Click() Dim f As FileDialog Set f = Application.FileDialog(msoFileDialogFilePicker) With f
I'm looking for one macro code in order to generate the excel files and give the standard excel file names.
Example: I have one master file in the given path like C:Temp and input box required to assign the number of file option. If I declare 10 files then my master file should get 10 copies and save it the above path....
I have an excel database where I register cases. I have in it a button that creates a folder with and ID nr that is in column A (I create new ID nr in the next row, when I press the button it will create a folder with that ID nr and inserts a blank word document in it). We have a template that we copy to the folder (depending what type of case). The idea would be that once the template is filled in and ready to print, It would take the values from the ID nr and a reference number a few cells to the right. Is it possible to tell excel to open the word document in the folder and create a PDF version with the ID nr and reference number. (there are only 2 templates, so the macro would have to look for one of the two in the folder)
The name of the templates are: "Standard" and "Other".
I guess the best way to start maybe this would be that I select the cell with the ID nr and then press a macro button to have this done.
One thing that needs to be done, is to put a copy in the same folder and another in a second folder called "Binder" in my documents folder.
I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.
Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.
In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".
Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)
Is it possible to write vba code that will generate a text file with ALL changes that were made to an excel file. Ex. If Cell A17 = "Monday, June 4, 2012" and a user updates Cell A17 to "N/A", I would like to know what the value was before and after the udpate was made.
I have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.
A co-worker of mine is exporting files from Quickpen as excel files, but every time he names the files with any Uppercase letters in the files name, they are automatically changing to all lower case. Even when he tries to rename the files, they will not stay with any uppercase letters....I have searched all over and cannot find a solution. If he sends ME the file, I can open it in excel and save it with any cases.