The following macro is returning a 1004 runtime error.
FName1 is a book title PHOTOSHOP MADE EASY which is trimmed to remove blank spaces at the end.
FName2 is the date =Today() formatted dd/mm/yy.
When the 1004 error appears the filepath shown looks like this ...
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] ......
I'd like a macro to have the workbook save as
Numbers as of "today's Date"
and then close that workbook.
I already tried the following...
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...
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.
View 1 Replies View RelatedI'm working on the following
Workbooks.Open Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-" & Ucase(Format(DateAdd("y", 0, Date)), "YYYY-MM-DD")&".XLS"
ChDir "D:CommondataIBMmain"
ActiveWorkbook.SaveAs Filename:= _
"D:CommondataIBMmainBRANCHBURG-PRODUCTS-BOM-ALUMINUM-UPDATE-NAFTA.XLS", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close
Date: 2009-06-03
What I'm trying too do is open a file, make an exact copy and save it under a new file name.
My problem is in the date formula the day is not always the same. In the sample case it's 03 at other times the day will change.
is there a way too get this too work irregardles of what the day might be?
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.
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String
myPath = Sheets("Date").Range("C8").Text
fName = Sheets("Date").Range("C9").Text
Sheets("Sage CSV File").Copy
With ActiveWorkbook
.SaveAs Filename:=myPath & fName
End With'
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies View RelatedI presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
Here is the current
Sub SaveIt() ...
In the code that i have, vb is making me a excel file and saves it with the title i putted.
I want him to save my files like this: example dd:mm:yyyy
Here is the
Sub Export()
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"D:Sablon citiriCalea 13 Septembrie_Sablon citiri.xls", FileFormat:= _
xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Call MsgBox("Va rog sa asteptati pana se creeaza registrul de lucru.
Some of the words are in romanian.
The bold text ,if is possible, it should be like this: D:Sablon citiriCalea 13 Septembrie_dd:mm:yyyy.xls (the date when i do the saving).
I'm trying to write a VBA code to save a file with the word "northeast" and then today's date. So today the file would be saved as "Northeast - April 16, 2009". I can't seem to figure it out. Anyone have any ideas?
View 9 Replies View RelatedHow do I save a file with yesterdays date, within a macro. This is what i have now
Code:
ActiveWorkbook.SaveAs Filename:= _
"S:Marks dataRENAME ME.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
I want to replace Rename me with yesterdays date. When I run the macro every morning.
Is it possible to save a file with a date taken from a cell? For example:
I have an Excel file opened, in the cell A1 there is a date: 12.04.2003 (or any other date format). I want an Excel to save this document, but after I check the date of creation (modification) of the file under Windows, I want to receive the result 12.04.2004 (or any other, that was in the cell "A1" before pressing SAVE button).
I imagine, that there would have to be some external script running, that would change the system date for a moment, just after detection of pressing SAVE command.
Is it possible that each time I save a file it saves the filename along with date and time as the version of the file in a specific path
View 9 Replies View RelatedI'm working on creating a computerised invoicing system for where I work, and right now I'm creating a Day Book file.
I want the name of the file to simply be the date which it was created, in YYYYMMDD format. However, I keep getting the error message and am not sure what has gone wrong.
Please note that in the day book template, cell F1 contains the formula
=TEXT(E1,"yyyymmdd")
which may be useful to know in the later part of the code.
from the code below?
Private Sub DayBook_Click()
Select Case MsgBox("Proceed? (Ensure that a Day Book has not already been created)", vbYesNo Or vbQuestion Or vbDefaultButton1, "Day Book Creation")
Case vbYes
Dim strName As String
'copy today's date from invoice template
Range("H4").Select
Application.CutCopyMode = False
Selection.Copy
I want to create a macro that when executed changes the name of "File1" to "File1 - Edited 8-9-2012". With the date being today's date (variable). This file is going to be emailed to someone who will then execute this macro.
Is there a way to prompt the Save As window with the updated file name so the user can save the file where ever he/she wants?
I've got a simple save macro below and was looking for a way that when this macro saves the file can it add the system date and time to the name.
'ChDir "C:
ashfinch"
' ActiveWorkbook.SaveAs Filename:="C:
ashfinchNFC-ORDER.XLS", FileFormat:= _
' xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
' , CreateBackup:=False
I have this script that runs. and checks to see if anything is in C1. If something is, it saves...if it doesn't, it fails and does not save.
Sub SaveAsCell()
Dim strName As String
On Error Goto InvalidName
strName = Sheet1. Range("C1")
ActiveWorkbook.SaveAs strName
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
" is not a valid file name.", vbCritical, "TitleBar Name" ........
We have an excel file which is made every thursday for comparison purposes etc. I've made an macro (excel 2003) which - on start - asks via an inputbox for the extension in date format for saving purposes. Later on, the macro will search for the same file but from last week to open it and copy/paste a 'comments' column into this new one. All went well, everybody happy. However it may be well possible that file is made and saved but with an extra extension: example:
dir to save = C:file
file when starts runs inputbox "Please enter a date (e.g. 2008-06-20)"
The macro records this string as mydate:
Sub Start()
Dim AnyString
Dim MyStr
Dim DirString
Dim mynum 'As String
Dim resp As Long
Dim get_mynum
Dim mydate
'Define extension for the file name to be saved and the correct path (dir) where this file will be stored.
get_mynum:
mynum = Application.InputBox("Enter the filename's extension to save in yyyy-mm-dd (e.g. 2008-06-30)", vbOKCancel)
If mynum = False Then
msgbox ("You do not want to continue? Ok, programm stopped")
Exit Sub
ElseIf mynum = "" Then..............
I have a report which I have written a macro to reformat, I then want to copy the sheet (Remake) containing the report into a new workbook as the only sheet in the new workbook. I want the sheet name to be changed from "Remake" to "Duty to Consider" and I want the new workbook to save itself automatically as "Duty to Consider - Month/Year" - in this case it would be "Duty to Consider - August 2008".
I think that a Pop Up box which lets me input the Month and Year of the report I am working on would be useful in naming the new workbook, if this box popped up when I click the button which runs my Macro that'd be great. Here is the code I am using to reformat my report. This works fine I just need the changes I mentioned. Application. ScreenUpdating = False 'this will turn on screen updating, making the macro run faster
'This will select the DTC Tab and remove rows 1-4
Sheets("DTC").Select
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
'This will add a filter to the data to remove duplicate rows
Range("A1").Select
Range("A1:J1307").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'This will copy the data on the DTC tab and copy it into the Remake Tab
Cells.Select.....................
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.
Currently, when I book keep on a particular day, I open the template.xls workbook;
draft my journals;
and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.
With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).
Upon clicking a button, I would like the macro to;
Copy the active sheet into a new book and save the file as the current date.
Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.
In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.
I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.
I would like to 'save as' my current open file with a new name equal to the value in cell A1 of the active sheet, and save it to C:Apps. I would also like to save it is a pdf with the same name and to the same location. Would that be possible in the same macro?
View 4 Replies View RelatedI have a macro recorded that saves the workbook to my desktop and then saves an additional copy to a shared drive. Right now it wants to save my file as the same name every time and ask to replace the existing copy.
What I would like to tell it to do is make the save as name reference cell D10. Unfortunatley, I don't know how to tell it to do that in visual basic.
Would I be able to save a Macro button in a CSV File since every time I open spreadsheet it disappears and my system only accepts CSV.
View 7 Replies View RelatedSo I am trying to design a workbook that has two worksheets... one with instructions and a button for users to click to "Save as .CSV File", another for the data that will go into that CSV file.
Here are the Macro requirements:
1. The user will be prompted for the File & Location to save the .CSV file
2. If they click 'Cancel', no changes will be made (and unlike my current code, it won't ask them to debug).
3. Confirmation of the filename is not necessary even though it's currently included in my Macro
4. The file will automatically "reopen" so that they only see the new .CSV file without the original Instruction tab.
The code I wrote to save a file with the last row counter and month counter looks as follows (this was working on my Mac)
ActiveWorkbook.SaveAs FileName:= _
"Macintosh HD:Users:myratriegaardt:Desktop:Quotes" & M & ":Quote" & LastRow & ":QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
I converted it to put on Windows,
ActiveWorkbook.SaveAs FileName:= _
"C:UsersCape CratingDesktopQuotes" & M & "Quote" & LastRow & "QuoteHG" & LastRow & ".xlsm", FileFormat _
:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
This however gives an error.
Macro to save the worksheet every 5min
A macros is needed to:
1. save the worksheet under the same name
(& variation: under diff name - e.g. quotes_HHMM.xls ,
where HH is hour and MM is minutes of the time the file is saved)
2. every 5 minutes (the file is opened at 9AM and closed at 6PM manually)
I have a file, where the financial data is streamed in from Reuters.
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies View Related