Macro To SaveAs And Rename A File
Oct 15, 2009
I think my code is close but I keep getting an error "Run-time error '1004': Application-defined or object-defined error"
I am trying to rename the active workbook by appending yesterday's date onto the filename, using the Save As.
I might be going about this all wrong anyway. My user needs the macro to save, rename, and close the renamed copy of the workbook but to leave the original workbook open. I'm trying to do it in steps so I might be doing this the hard way. However, my code to Save As is below:
Sub SaveAsRename()
Dim CurrentPath As String
Dim CurrentFileName As String
Dim NewFileName As String
Dim Today As Date
Today = Int(Range("A3") - 1) 'Cell A3 contains the =Today() formula
CurrentPath = ThisWorkbook.Path
CurrentFileName = "QU Backhaul Dispatch "
NewFileName = CurrentFileName & Today & ".xlsm"
ActiveWorkbook.SaveAs Filename:=CurrentPath & "" & NewFileName
End Sub
My Locals window has all of the correct values right up to the ActiveWorkbook.SaveAs which is where it fails.
View 9 Replies
ADVERTISEMENT
Nov 13, 2008
I have the following Macro that I run assigned to a Button.
Sub AskAndDo()
If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD")
End If
End If
Application.Quit
End Sub.......
View 9 Replies
View Related
Feb 4, 2009
By Code I am trying to SaveAs the present Workbook : Only in the same directory as the present Workbook- but with a different file name. The new file name would be :"the Filename of the Active Workbook" + " ("D9") of the Sheet("ADMIN")". This macro will only be run from the Sheet("Admin").
View 4 Replies
View Related
Jun 16, 2014
I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code] .......
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
View 7 Replies
View Related
May 10, 2008
I'm using a VBscript that will copy a worksheet and it works perfectly except that it saves the copy to the same directory as the original workbook. I need it to save to a different directory.
What I believe is the pertinent part of the code that needs adjusting is:
"Input box to name new file
NewName = InputBox("Please Specify the name of your new workbook", "New Copy")
' Save it with the NewName and in the same directory as original
ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "" & NewName & ".xls"
ActiveWorkbook.Close SaveChanges:=False "
How would this be changed to specify that "NewName.xls" be saved to: D:/newdirectory/copies
View 9 Replies
View Related
Dec 13, 2006
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.
View 4 Replies
View Related
Nov 24, 2009
I have an excel template that needs to be copied multiple times and each sheet needs be named according to a list in an excel spread sheet. I also have a formula in the template that needs the value copied instead of the formula.
I got this script from an site and tried it. It runs but I don't see any spread sheets.
strComputer = "."
Set objWMIService = GetObject ("winmgmts:\" & strComputer & "
ootcimv2")
View 9 Replies
View Related
Apr 15, 2014
I have an Excel Worksheet (let's call it "AA.xlsm") which uses VBA code to loop through column A, update column B with some results/values, and then (for each value in column A) SaveAs the worksheet into a different filename/path as a ".csv" type file.
My line of code for the SaveAs is as follows: Workbooks("AA.xlsm").SaveAs LEpath & CurrFldr, FileFormat:=xlCSV
My problem is that my original worksheet, AA.xlsm, gets closed whereas I need it to stay open/active after each SaveAs iteration.
View 3 Replies
View Related
Oct 19, 2005
How can I set the SAVE AS file name to equal A1's cell contents? (More specifically, when I need to rename an existing open file and place my cursor in A1 and hit Save As, I need to new file name to automatically populate A1's cell contents so I don't have to retype the contents of A1.)
View 1 Replies
View Related
Nov 21, 2006
I have the following
Private Sub cmdSave_Click()
SaveRow
On Error Goto ErrorHandler
ThisFile = Sheets("Data"). Range("B2").Value
ActiveWorkbook. SaveAs Filename:= _
"C:Documents and SettingsMy Documents" & ThisFile, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks.Close
ErrorHandler:
MsgBox ("Error locating file. " + Err.Description)
End Sub
This saves a workbook with the File Name based on a cell in a worksheet in the above Path Name.
Is there a way that I can change this code to ask for the Path Name with the File Name, from the worksheet, as a default.
View 9 Replies
View Related
Jan 28, 2009
I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
View 2 Replies
View Related
Jul 8, 2013
I just copied and modify this code; What I want is to add sumting on the file name, without changing, file path, original file name and file extension
Say : I get the file
D: DocsMemo.docx
I can paste it to the fnpath with a changed filename
C: \_Temp Memo_BPMC-0001.docx
Heres the code
[Code] .....
View 3 Replies
View Related
Jun 26, 2008
I've written most of the code that I need... the problem is that I don't know how to properly select, move and rename the pdf from excel.
I have a popup to select the file, and I know what I want it to be called, what I need is the code to move and rename a non-excel file from inside a macro.
Here is what I have so far:
Sub AttachPDF()
Dim LastName As String
Dim Street As String
Dim callDate As String
Dim FilePath As String
Dim HouseNumber As String
Dim FullFileName As String
Dim FileName As String
Dim Scan As String
Range("D32").Select
'Ask user what file they want to attach.
Scan = Application. GetOpenFilename(FileFilter:="Follow Up Scan, *.pdf", Title:="Please Choose A PDF To Attach")
If Scan <> False Then
View 5 Replies
View Related
Mar 15, 2014
I am using this code for 2nd coping my file and rename it automatically......
[Code] .....
I want when i want another copy always my path should be "C: estabc.xlsm"
and
For new file in same path "C: estdef.xlsm" an input box ask for the name("def.xlsm") "what new name u want for this new file".
And I input the new name for my file and done.
View 4 Replies
View Related
Sep 24, 2009
I've found these functions to assist in renaming a file.
However, I'm not quite sure how to apply to code.
View 2 Replies
View Related
Oct 17, 2011
I have some code which will look for a specific file in a given location. Is it possible to use VBA to rename that file without opening/re-saving and killing the specific file?
View 8 Replies
View Related
Feb 22, 2012
I am trying to write a VBA that rename an Excel file by adding todays date at the end of the file name. For example rename the file
C:Documents est.xls to C:Documents est020221.xls 020221 is todays date
View 3 Replies
View Related
May 3, 2012
how to write a macro in excel to rename a file with a date in a cell (A1) from that file?
View 5 Replies
View Related
Jan 25, 2008
I am using the following code to rename a file that is being pumped out by another program.
Sub CopyAndDelete()
Name "C:Program FilesPATH1234567890_3.tvr" As "C:Program FilesPATHNEWNAME.tvr"
'
End Sub
The problem I have is the the file being pumped has a varible name. The nly constant is the _#.tvr. The first 10 digits are random numbers.
I have persued the "rhymn and reason" for the file name with the vendor and have been told it can not be changed and is random. The "_#.tvr" piece comes from the number of reports I have generated for the day. So I have 5 prefined reports that run each day and can recreate the _0.tvr through _4.tvr but the first 10 are random each time.
I would like for the rename to "ignore" the first 10 digits then looking at the file name.
View 9 Replies
View Related
Dec 12, 2008
I have the following code, which I use to open up a .xls file generated from Crystal Reports:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fexport1 As String ' variables for the exported file
Dim fexport2 As String
Dim wb1 As String 'variables to change between the opened workbooks
Dim wb2 As String
strTemp = "Please Choose The Exported File"
MsgBox strTemp
fexport1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls)")
If InStr(fexport1, "False") = 0 Then
Workbooks.Open fexport1
wb1 = ActiveWorkbook.Name
Else
strTemp = "Operation Canceled"
End If
The problem is that Crystal Reports generates the file with an invalid worksheet name (it contains a backslash) and I do not have access to modify the Crystal Report. Althought the file can be automatically repaired by Excel when manually opened, the macro chokes and the "Application.DisplayAlerts = False" does not fix it. The only solution I can think of is renaming the worksheet without opening the file.
View 9 Replies
View Related
Jul 25, 2014
I am trying to move and rename an excel template file using a macro. My code works fine when it is moving the file locally on my C: drive. However, when I try to do it on a mapped network drive I get a Path/File access error. Here is the relevant code:
[Code] ......
I tried it both ways that are commented out - both give me the error. I have permissions to read/write in all relevant folders. What am I missing here?
View 2 Replies
View Related
Jan 24, 2008
I have the following
1) directoryA
2) ColA - Contains oldFilename
3) ColB - Contains newFilename
I simply need to rename files from values in colA to colB working with directoryA with the extension of .jpg
View 9 Replies
View Related
Oct 19, 2006
I've added an word object to my excel sheet as an icon. The only problem is that it shows the word icon then under it says 'Microsoft Word Document'. This might be an obvious and silly question. But how do I Rename the 'Microsoft Word Document' to a title of my choice?
View 2 Replies
View Related
Feb 15, 2007
I want to restrict users to just read or print a word document. Document is very confidential so we dont want them to copy or manipulate the document.
I thought of giving password to restrict to read only option but that still lets users to copy and paste on to a new document.
View 9 Replies
View Related
Mar 14, 2014
I want a macro. when i run this macro pop up a input box and ask for copy of master copy and asked for rename the file...
E.g. I have a master file in "z:42766decmasterfile.xlsm
When I click macro then macro do a copy of masterfile and rename it according to choice...
View 4 Replies
View Related
Jan 15, 2013
I am trying to copy a file, rename it, and save it to a new folder. I keep getting a "Compile Error ; Syntax Error" at line FileCopy (ImagePath & oldName, NewPath & newName). I am using Excel 2010.
FileCopy (ImagePath & oldName, NewPath & newName)Sub RenameFiles()
'Renames file based on "sheet 1" - Column 1 Old file name - Column 2 New file name
Dim oldName As String
Dim myfile As String
Dim newName As String
Dim ImagePath As String
Dim NewPath As String
[code]....
View 5 Replies
View Related
Jul 7, 2009
Is it possible to have a macro to prompt the user for a tab name, check if it exists and if so copy it and ask the user for the new name for it to be rename to?
View 8 Replies
View Related
Sep 10, 2009
From my research on trying to apply this function, I have peiced together several codes and am unable to get my end result. I want to add a worksheet and rename it with text formula on my main "Macro" sheet from cell D1. The name in cell D1 is a date that changes based on a formula. I can get the macro to add the worksheet but then it stops at renaming it:
Sub NewTab()
'
' NewTab Macro
' Macro recorded 9/10/2009 by andrea_1
'
'
Dim wsNew As Worksheet
Set wsNew = ThisWorkbook.Worksheets.Add
ActiveSheet.Select
ActiveSheet.Name = Worksheets(Macro).Range("D1").Value
End Sub
View 2 Replies
View Related
Aug 24, 2012
I run a report each month which renames the tab name called ex. "xxxxx Report 8". The 8 meaning for the month of August. I have created a macro to rename the tab, but each month when i generate the report the tab name change, hence i would have to change my macro again to reflect the tab name. What formula could i use in VBA to change the name of tab so i would not have to change my macro each month?
I currently use as shown below, which does not work since the number changes each month when i generate the report):
Sheets("xxxxx Report 8").Select
Sheets("xxxxx Report 8").Name = "Monthly Report"
View 4 Replies
View Related
Aug 2, 2008
Is it possible to have a macro re-name a worksheet. The context I want to use it would almost as a conditional formula.
Example: IF A1 = "time sheet" then sheet1 is then re-named to "time sheet"
View 9 Replies
View Related
