I am trying to write an Excel VBA to save my file with a cell name when I click a button. The Excel file I am working with is a Macro Enabled Template file (.xltm). I don't want Excel to save the file as a.xltm file, rather I want it to save the file as a.xlsm file. Aug 02, 2013 Hi All, I have hundreds of files in a folder which I have to add file modified date as suffix to existing file name. How to use VBA to execute that tasks. For example: Filename Last Modified on 2013.02.03 - 'SaleList.xls' Target filename- 'SalesList 20130203.xls' Put this code into a standard code module in a workbook, and save the workbook in the.
Dear All, I am looking for VBA to do a Save As on the Excel file and append the current date to the end of the file name. For example, if the file name is 'Accounts Payable' and the current date is 18 July 2011, I would like the Save As file name to be 'Accounts Payable 20110718' (YYYYMMDD sequence for the date). Also, if the user selects Cancel from the Save As dialog box, I do not want the macro to return a Run-Time Error 1004 as it does now. I would like it to cancel the Save As operation and continue on in the macro. Here is my current code: Code: I am using Windows 7 and Excel 2010 Thanks, GL Free Excel CoursesSimilar Excel TutorialsGet the Current Date in Excel How to get the current date using Excel. This method updates the date every day so that it is always accurate. To ... Print the Current Date or Time in Excel How to print the current date and or time on an Excel spreadsheet. This is a simple technique that allows you to di ... Display the Current Day of the Week in Excel Use a formula or formatting to display the current day of the week in Excel. Sections: Formula to Show Current Day ... NOW() - Display The Current Time in Excel This Excel tip will show you how to display the current time within any Excel spreadsheet. This is a useful functio ... Subscribe for Weekly TutorialsHelpful tutorials delivered to your email!Helpful Excel MacrosSave the Current Worksheet as a New File in the Current Folder - This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from Save the Current Worksheet as a New Excel Workbook File - This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a Excel Macro to Save a Specific Worksheet as a New File - This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a Disables the 'Save As' Feature in Excel - This macro will disable the Save As feature in excel. This means that a user will not be able to resave the workbook un Prevent Saving a Workbook under a Different File Name - This Excel macro prevents the user from saving an excel file or workbook under a different file name. The file can only Similar Topics
I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop. How do I find out what the current User's desktop folder path is each time the Marco is run by a different User? Example User's path: 'C:Documents and SettingsjfarcDesktop' Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro. Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory. I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path: Dim wbThis As Workbook Set wbThis = ThisWorkbook ChDir wbThis.Path
Arcangelo from Italy asks: How can I write an Excel VBA macro to save the current Excel file with a filename derived from cell A1? This macro is amazingly straight-forward: Public Sub SaveAsA1() ThisFile = Range('A1').Value ActiveWorkbook.SaveAs Filename:=ThisFile End Sub I'm totally lost on this 'amazingly straight-forward' macro!!!! Could someone help if I tell you the SAVE location? It's T:COMMISSIONINGIJTTIMELOG projectStaff#1. I'm just not certain what value I'm replacing in the Macro above. Any help appreciated.. I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet. Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean: using Save doesn't appear to do anythingusing Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File')if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again.I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times. The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause. Anyone have any idea? Cheers Jon Edit: I have read this: http://support.microsoft.com/kb/271513 Doesn't seem to cover the issue I describe
I am using the code below to disable the save function very successafully. However, is there a work around to allow a macro to save? ---------------------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'this disables the save function on the XLS MsgBox '****Save is Disabled****' ' Following line will prevent all saving Cancel = True ' Following line will prevent the Save As Dialog box from showing If SaveAsUI Then SaveAsUI = False End Sub
I was wondering if there is a way of getting a macro to open the 'save as' dialogue box and then have it fill in the 'file name' field. I dont want it to actually make the save though as I want the user to have the chance to check that the save location is correct and the option to change the filename if required. Cheers.
Hello, I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one. Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder. I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename). I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!! Thanks, Jason
I have a template that automatically populates the date field with the current date when opened, using today(). Users then Save As to have an archive copy of their spreadsheet. However, when they go back to access their saved sheet, the date changes from the created date to the current date. How do I stop this in Excel? I know that Word lets you do this by changing the code from {DATE} to {CREATEDATE}, but I can't find a similar setting in Excel. Please help and thank you!
Hey guys, I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code - Sub PrintPDF() Filename = 'C:Documents and SettingssambMy Documents' & ActiveSheet.Range('Z1').Value SendKeys Filename & '{ENTER}', False ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ 'Adobe PDF:', Collate:=True End Sub The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!
Can anyone help me with creating a macro that will save the worksheet as a pdf in a specific folder with the value of a cell and the date. This is what I have so far. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range('H10').Value & Format(Date, 'mmdd') This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive. Any help would be greatly appreciated. THANKS
I have workbook in which I want to save a specific worksheet to a new file with only the values saved - all data in this worksheet are references to cells on another worksheet, which is using VLOOKUP to pull data from a database. Found the following code and it gets me close, but it copies the cell references, not the values. It also allows me to specify the file name from a cell reference. I want the new workbook file to simply be saved, not opened, and a message box to display stating where the file was saved (will always be in the same location on the LAN). What modifications do I make to this to get this to work per above requirements? Sub CopyMe() Dim SaveMeAs As String SaveMeAs = Sheets('Sheet1').Range('B2').Text Sheets('Sheet3').Copy ActiveWorkbook.SaveAs Filename:='C:My Documents' & SaveMeAs End Sub
I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. I want to be able to just highlight the selected range of cells I need to insert into and hit a macro button and have the date and time inserted into just the cells I have highlighted. I'm not sure how to make this work with just the cells I've highlighted. Any help any one can give me would be greatly appreciated. Thanks! Mike
I know this question has been asked a bajillion times, so I apologize for the redundancy. I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back. Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to 'stick' so that they don't revert back to scientific format when I reopen the file? Thanks so much for your help!
I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells. I'm new to macros and would appreciate any help this board can offer. Thanks...
We have a large (4,000+ rows) excel worksheet from a prior employee that I would like to be able to use, but it is password protected and no one has the password. Is there a way to copy or save it as a different file that will not copy the password protection? It would save me a couple days of work if it is possible. Thanks
Hi all, I've been getting this error on occasion recently. - It's a shared document - There is usually 10-12 people using the file at the same time I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it. Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error? Thanks! Hello everyone!!!! Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO We have a 'shared' work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a 'refreshed' copy of the workbook before they start writing in cells that already contain information. How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them. Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.) Thanks so much!!!!! Muchly appriciated. I need help desparately. Last night I spent several hours working on an Excel 2002 spreadsheet and it has disappeared. I saved it routinely as I was making many changes updates etc. I opened original file --stuff.xls --- made changes and saved it as again as stuff.xls. I have done this several times before. Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have done a search, for any file modified since Thurs and nothing has appeared. I have checked the recycle bin, this did not go into auto recover nothing of the like. I am just traumatized, because I have NEVER had anything like this happen before. I even thought perhaps I didn't save it as often as I thought, (I know that I did) but I remembered that I saved it at least once and I can't even find an Excel file that has been modified since Thurs!!! Does anyone have any idea what could have happened to this file?? BTW, I have prayed, rebooted, run spybot, called witch doctor, all with no success. I have never had a file to just disappear with no trace. Any help would be greatly appreciated. Thanks in advance---
I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of '=D6+C6' and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of '=D7+C7'. Example: A1: 50 A2: 10 B1: 60 B2: 20 A3: Formula: =A1+A2 Displays: 60 Right click A3, Copy, right click B3, paste A3 displays 60 When I click save, it will change the display value to 80. I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself. Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value. I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem. Any ideas how to fix this? I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code: So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either! The code I'm using is Code: Any ideas what could be wrong with either approach? Thank-you I have an Excel workbook that was created by a former coworker. It includes a macro that, among other things, displays a message box about the 2008 file. The macro runs as soon as the file is opened. I'd like to access that macro to correct the date to 2011 and see what else, if anything, it's doing for me (it doesn't appear to do much). I can find references to creating macros to hide and unhide rows/columns and I found ways to delete all macros in a workbook, but I cannot find anything about unhiding a macro without knowing its name. Does anyone know of a way to unhide this macro? Thanks!
Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time.
I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size. But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else. Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file. Thank you very much for your help.
file locked for editing , can I kick the other user out if its my file? I have made an excel file where other people have to go in and update it. This process takes 2 secs but some people forget and stay in it over lunch etc causing a great annoyance. Is there anyway I can 'kick' them out my file? Im not concerned if they lose any data they've entered as I'm sure this will help them get the message to get out the file quicker.. Its really doing my head in... pls help
Hello, please help me out with this one. So I am at work workin on this report and I need a function that will make Excel automatically insert the today's date in a cell (let's say D90) when I fill the content of another cell (for example C90) and the that will be inserted will never change. I tried to use the =TODAY( ) function but it keeps updating the date in the cell to the current date if i reopen the worksheet 2 days later. I need the cell to keep in the cell the date of the day when I filled in the content of cell C90 and do not updated it every time i open the document to the actual date.. Thanks in advance!! We have an Excel spreadsheet that sits on the network. People need to open the file to be able to sign up for various duty rosters. We would like for the file to open for the first person. And then for any others after that, get a message that the file is in use WITHOUT the option to open a read-only copy. Our staff can't read and they keep opening additional copies of the file! I have read about sharing the file and I don't think that would make things any better. Thanks, Lynn |
Hello,
Thank you for posting.
Using FSO (FileSystemObject) can be a good way to do this. It provides access to a computer's file system.
Please paste the following code to a Module:
Option Explicit
'##########################################################
'[Function Instruction]
'Convert a general path only when it doesn't end with '.
'[Example]
' D:Tech >> D:Tech
'##########################################################
Public Function CGPath(ByVal strGPath As String) As String
If Right(strGPath, 1) <> ' Then strGPath = strGPath & '
CGPath = strGPath
End Function
'[Function Instruction]
'Convert a general path only when it doesn't end with '.
'[Example]
' D:Tech >> D:Tech
'##########################################################
Public Function CGPath(ByVal strGPath As String) As String
If Right(strGPath, 1) <> ' Then strGPath = strGPath & '
CGPath = strGPath
End Function
'######################################################################
'Get the specified string section which is divided by its sub-string.
'######################################################################
Function Get_nStr( _
ByVal wStr As String, _
ByVal Mark As String, _
ByVal n As Integer, _
Optional ByVal Chk As Boolean = False, _
Optional d As Integer) As String
Dim wArr
If (wStr = ') Or (Mark = ') Then Exit Function
If Len(Mark) > Len(wStr) Then Exit Function
wArr = Split(wStr, Mark)
d = UBound(wArr) + 1
If d = 1 Then
d = 0
Exit Function
Else
If n < 1 Then Exit Function
End If
If Chk = True Then n = d - n + 1
Get_nStr = wArr(n - 1)
End Function
'Get the specified string section which is divided by its sub-string.
'######################################################################
Function Get_nStr( _
ByVal wStr As String, _
ByVal Mark As String, _
ByVal n As Integer, _
Optional ByVal Chk As Boolean = False, _
Optional d As Integer) As String
Dim wArr
If (wStr = ') Or (Mark = ') Then Exit Function
If Len(Mark) > Len(wStr) Then Exit Function
wArr = Split(wStr, Mark)
d = UBound(wArr) + 1
If d = 1 Then
d = 0
Exit Function
Else
If n < 1 Then Exit Function
End If
If Chk = True Then n = d - n + 1
Get_nStr = wArr(n - 1)
End Function
Public Sub CopyAndRenameFiles2PreviousMonth(ByVal strOriRootFolder As String, ByVal strNewRootFolder As String)
Dim sFile As Object
Dim sFolder As Object
Dim fso As Object
Dim strTemp As String
Dim strFileNameNew As String
Dim strFileNameOld As String
strTemp = ' ' & Format(Now - Day(Now), 'mm-yyyy') & '.'
Set fso = CreateObject('Scripting.FileSystemObject')
Set sFolder = fso.GetFolder(CGPath(strOriRootFolder))
For Each sFile In sFolder.Files
'/* Get the old file name .*/
strFileNameOld = sFile.Name
'/* Replace the public string 'Current' in the old file name.*/
strFileNameOld = Replace(strFileNameOld, 'Current', ')
'/* Set the new file name of the previous month. */
strFileNameNew = Get_nStr(strFileNameOld, '.', 1) & strTemp & Get_nStr(strFileNameOld, '.', 1, True)
'/* Copy to the new root folder.*/
fso.CopyFile sFile.Path, CGPath(strNewRootFolder) & strFileNameNew
Next
Set sFile = Nothing
Set sFolder = Nothing
Set fso = Nothing
End Sub
Dim sFile As Object
Dim sFolder As Object
Dim fso As Object
Dim strTemp As String
Dim strFileNameNew As String
Dim strFileNameOld As String
strTemp = ' ' & Format(Now - Day(Now), 'mm-yyyy') & '.'
Set fso = CreateObject('Scripting.FileSystemObject')
Set sFolder = fso.GetFolder(CGPath(strOriRootFolder))
For Each sFile In sFolder.Files
'/* Get the old file name .*/
strFileNameOld = sFile.Name
'/* Replace the public string 'Current' in the old file name.*/
strFileNameOld = Replace(strFileNameOld, 'Current', ')
'/* Set the new file name of the previous month. */
strFileNameNew = Get_nStr(strFileNameOld, '.', 1) & strTemp & Get_nStr(strFileNameOld, '.', 1, True)
'/* Copy to the new root folder.*/
fso.CopyFile sFile.Path, CGPath(strNewRootFolder) & strFileNameNew
Next
Set sFile = Nothing
Set sFolder = Nothing
Set fso = Nothing
End Sub
Sub CallAction()
CopyAndRenameFiles2PreviousMonth 'ns-warCurrent', 'ns-warJan'
End Sub
CopyAndRenameFiles2PreviousMonth 'ns-warCurrent', 'ns-warJan'
End Sub
For testing, you can run the Macro CallAction(), and then you'll get the result.
Cheers,
Cristin Yan