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 an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.
want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?
'Insert Date Information Dim vDate As Date Range("D2").Select ActiveCell.FormulaR1C1 = Date vDate = Date
'Get date from user Dim dDate As Date vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _ & Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _ Title:="Due Date", Type:=1 + 2).............
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.
I have written a sub to open a txt file, but I want to then save it as Excel, with a filename (a variable). The variable is public, and is set by other subs. I have added the standard "save as..." button to my custom toolbar to use for this and the following code (taken from previous thread)
Public Sub SaveFile(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = True Then Application.EnableEvents = False Application.Dialogs(xlDialogSaveAs).Show MyVariable, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Cancel = True Application.EnableEvents = True
End If End Sub
But it comes up with the original filename in the save as dialogue and txt file type. Any help would be great, this is used by inexperienced users and I don't want the original file saved as it's prevoius name and type
i try to write a code to pop up input box with browse button to select the file path the code without browse button is workin corectly
Private Sub CommandButton3_Click() Dim N As Integer Dim Fname As String Fname = InputBox("Please enter the file name and path to save the output", "enter file name") Sheets( Array(Sheet2.Name, Sheet3.Name)).Copy ActiveWorkbook.SaveAs Filename:=Fname ActiveWorkbook.Close End Sub
Need to add browse button to make it easier than writting the file path
i have this code to run autofilter in protected sheet2, assigned to a check Box, but it gives me this Msg when i run the code by checking the check Box: File in use. The file is locked for editing by the (my name). However, when i excute the code by F5 in the VBE window, it works fine also when i excute the code from Worksheet_Activate , it works fine
Sub t() With Sheet2 . Protect "0", , , , userinterfaceonly:=True, Allowautofilter:=True .AutoFilterMode = False .Range("A5:DT5").AutoFilter End With End Sub
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.
Date = InputBox("Enter the production month date in YYYYMM format", "DATE") 'This is the only info I want them to enter
Windows("PCN4.xls").Activate Sheets("Bring the data in here").Select Range("A" & NextLine).Select With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;W:C30MonthlyMonthly NEW TCs200610 otconsa.txt", Destination:=Range _ ' I only need the 200610 to change every month ("A" & NextLine)) .Name = "totconsa" End With
I create a macro which ask the user to input a formula(user can select a range in the excel screen), then I copy this formula to a specific row of every worksheets.
The problem is Application.InputBox("Insert a Formula", "This accepts Formula", 0+2) can not return the formula, it will automatically evaluate this formula and return a value!
This code is:
Sub CreateNewItem1() Dim bFormula As String hi=10 Application. ScreenUpdating = True
' here is the INPUTBOX-method : On Error Resume Next Application.DisplayAlerts = False bFormula =Application.InputBox("Insert a Formula", "This accepts Formula", 0+2 ) On Error Goto 0 Application.DisplayAlerts = True MsgBox "You have inserted bFormula is:" & bFormula
I have an input box for a password, and my vb code checks the password against a list, and if it is correct allows the user to continue. I want to change the input box for a user form, as it allows more text on the form, and I also want to include a checkbox that the user must tick to show that they have read and understood the instructions. I have defined the form, but am having trouble getting the form to interpret the user input.
The form has one checkbox, which needs to be ticked to continue, otherwise give a message saying that you have to confirm. It also has one textbox, for the user to input the password. I dont understand how to name the contents of the text box, and use it in the following code. Also do I need a "send" button on the user form, for the user to click after entering the password. If so how does that work?
I am trying to prompt the user for the directory to be saved in and file name to be saved as; then save the workbook in the input directory with the inputted file name.
I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.
The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?
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?
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.
I'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 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.
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.
