Date Not Being Updated When Open File
Apr 2, 2014
Previous to a PC meltdown a few months ago, when I opened my monthly customer list file the account date would automatically update to the current date. After the PC repair, reinstall of MS Office 2002 (same version as before the PC death) and copying of documents back to their old locations, I now have to update this manually for all accounts each time I open it. I have searched through the options and customize menus but there is no mention of this setting. Before, it just did it from day one (5 years ago) without any setup.
How do I get back the old auto-date update preference?
View 1 Replies
ADVERTISEMENT
Nov 20, 2006
I have created a spreadsheet and saved it as a template. when the staff open it to enter information they use save as and give it a name. They only need open and enter information once. I have put into this spreadsheet in cell A1 a formula to enter the days date. This all works fine. A few days later I need to access the spreadsheet to check the figures entered and obviously the date changes to the date when I open it up. Is there any way of stopping this happening without using macros. i.e. keep the date as the day they entered the information.
View 9 Replies
View Related
Jun 4, 2009
I'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?
View 9 Replies
View Related
Feb 8, 2013
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
View 1 Replies
View Related
Apr 24, 2014
I need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls
Example: statistics_20140423_142754.xls
Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?
This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.
View 4 Replies
View Related
Feb 25, 2009
how can you automatic the date to today's date when you open the excel file?
ie.
Price Report For 02/25/09
View 5 Replies
View Related
Jul 26, 2012
I am trying to write a macro to complete a report for me. I have everything working and tested but I have 1 issue. The report needs to take some data from yesterdays report.
Therefore I need a code which is able to open the file, I know how to do this with a constant file name but the issue arises because the file from yesterday is saved with the date.
i.e. filename250712 etc..
View 3 Replies
View Related
Nov 19, 2004
Probably not possible but can you write a macro to load a file which has a file name which always starts with Act2July followed by the date
i.e. Act2July191104.xls
but next friday it will be called Act2July261104.xls
This file is created by the network.
So can the macro pick up the Act2July part of the file name and then select the file dependent on when it was saved, so that the latest update is always loaded.
View 9 Replies
View Related
Aug 6, 2013
I have a csv file named "BB31_2013-08-01". I would like to open this file via vba and this is not my only file. I also have another similar fileames such as "BB31_2013-08-08", therefore, I try to create some function like yyyy, mm & dd where I can modifie it easily for next file.
Here is my code:
VB:
'change weekly
yyyy = "2013"
mm = "08"
dd = "01"
Workbooks.Open Filename:= _
"C:Documents and Settings1314228DesktopTest ProgressBB31& yyyy &-& mm &-& dd.csv"
[Code] .....
View 3 Replies
View Related
Aug 6, 2014
I have this code, it's working to open the last modified file but how can I implement that it also opens a certain file name.
examples
Test1 150126.xls
Test1 150128.xls
Test1 150140.xls
the numbers are the time the file is saved, is it possible to also look at file name:text1 and then look at the last modified file So only fill in the file name and the macro opens file: Test1 150140
[Code] .....
View 5 Replies
View Related
Apr 16, 2014
I have the current code that opens our raw data file and copys it into my workbook. However it now turns out the file is saved everyday as a new file with todays date on the end. Here is my current code:
Sub CopyRaw()
Application.DisplayAlerts = False
Sheets("PROD_SELECTION").Visible = True
Sheets("PROD_SELECTION").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
[Code] .....
Todays file is now in the name "I:SalesSales and MarketingMarketing List Product Selection Details (20140416)"
I know i can do this somehow using the Date function but the closest I have got is using
"YEAR(NOW())&RIGHT(MONTH(NOW())+100,2)&DAY(NOW())"
To generate todays date like so: "20140416" but I dont know how to get that into my code correctly so i open the file with that on the end.
View 1 Replies
View Related
May 29, 2014
I need a code which will open up a previous days file. I have the code to pick the correct file but I am stuck because the folders are multiple levels down to year then month.
Eg. Folder Name > YYYY > Month
So i need to have a code which can look in to the relevant folder based on the current date and then be able to open the file i need.
I.e. today is 29May so i need the code to open the 2014>May folder.
For 1st day of month I would need it to open last months folder I.e. 01June Open May folder
View 2 Replies
View Related
Jun 5, 2009
Is there a way to open a file similair to
fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application. GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
I want to filter the filenames to only certain prefixes
I am then importing the files and populatin the ws
Here is my whole module so it can be seen in context
Sub GetBCDV()
Dim varFiles As Variant
Dim SourceBook As Workbook, TargetBook As Workbook, strAddress As String ', 1 As Integer
Call Archive_WB_BCDV
fMask="Post Biz Voice Installs QC Report*.TXT"
varFiles = Application.GetOpenFilename("TEXT Files,"&fMask, 1, "Please select all the Post Biz Voice Installs QA Report files", , True)
View 5 Replies
View Related
Feb 29, 2008
I have manged to search for the last updated file on a folder but i want to use the filename as the source for a copy and paste. it all seems to work bar the linking of the 2 processes the error message 'run time error 9 subscript out of range' is the file is already open should it just not use the same variable to copy the files?
Option Explicit
Sub Get_Availability_Data()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim wsTo As Worksheet, wsFrom As Worksheet
Dim myDir As String, fn As String, a(), n As Long, myFile As String
Dim myDate As Date, temp As Date
myDir = "C:BarkingEMCIN"
fn = Dir(myDir & "*.csv")
Do While fn <> ""
temp = CreateObject("Scripting.FileSystemObject").GetFile(myDir & "" & ................................................
View 9 Replies
View Related
May 5, 2008
I'm looking for a code which is checking the file name first before opening and operating on it. File name format is filenameyyyymmdd.xls but the problem is files in that directory are saved irregularly (couple times a week) and I need to open the newest saved file.
View 2 Replies
View Related
Sep 15, 2014
I managed to create some code that will connect to a workbook and take the data from there.
It is kind of SQL mixed with VBA. I just got this by finding it in google (I don't have the link handy now).
What the file does, is it will take 2 columns from a workbook called "Test.xls" and then take out 2 columns "Name" and "ID".
The only thing is, this is only a test and not the actual files I need to open. the files are always saved on the same folder, but the name is the day of the export of it. So when I export a file today, it will get 2014.09.10.xls. The sheet (only 1) will also get that name.
How could I change the below code that it will take that file based on today's date and read from the sheet with today's data?
VB:
Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
'DBPath = ThisWorkbook.FullName
[Code] ....
View 9 Replies
View Related
Jun 22, 2007
I have developed a system for logging phone calls in Excel which will be used by a team of 3-4 people in a small call centre type environment. Each operator will have their own copy of the application which will independently contain the data for their own calls.
However, what I need to do is have some way of regularly updating everyone’s data to a central workbook so that all the operators have access to reasonably up to date information from all the other operators.
I can handle the actual transfer of data from one book to another and perhaps using an OnTime procedure to handle the regularity, but the problem I have is that with regular updating, there will inevitably be read-only issues which are going to make the application crash.
I was thinking maybe have the data update run from the central workbook itself to avoid this, but was wondering if anyone had any bright ideas. Some sort of file sharing or something maybe?
View 9 Replies
View Related
Mar 17, 2009
I have a spreadsheet that references another spreadsheet, however the other spreadsheet will get updated from time to time. When it's updated the file name changes to reflect the revision:
"C:Projects9-022_Tunisia1_Design9-022-R00-Inst_Database.xls"
When this file is revised the "...R001..." will increment with each revision but the rest of the file will stay the same.
Sometimes I have to reference a file in another directory:
"C:Projects9-030_Brazil1 Design9-030-R00-Inst_Database.xls"
I was planning on placing information in the following cells of this spreadsheet ("C:Projects9-022_Tunisia1_Design9-022-R001-Cause_&_Effects.xls):
Cell A1 - Project Number (i.e. 09-022, or 09-030)
Cell A2 - Project Name (i.e. Tunisia, or Brazil)
Cell A3 - File Rev Number (i.e. 1, 2, 3, etc.)
I was thinking my "INDIRECT.EXE" would look something like this:
=INDIRECT.EXE("'C:Projects"&A1&"1_Design["&A1&"_"&A2&"-R00"&A3&"-Cause_&_Effects.XLS]Index'!Q5")
The value on the spreadsheet tab "Index" that I'm referencing in cell Q5, is "FG-5901" but all I'm getting in the spreadsheet were this formula resides, is an error "#NAME?"
Part of my problem is that I don't fully understand the function of the "&" symbol in the formula. Sometimes I've seen it placed on both sides of a cell reference (i.e. &A1&), and other time only at the being (i.e. &A1).
View 6 Replies
View Related
Aug 22, 2008
way to generate automated email whenever excel file is updated by any one.
View 14 Replies
View Related
Apr 7, 2009
If I have a spreadsheet with some data loaded in through XML and viewed as a
map, what is the VBA command to reload/reimport the source XML data?
The scenario is that the source data (XML) may change at any time, and I would like to reflect this to the presentation layer (excel) at regular intervals. I have a loop set up but cannot find the command to reset the source XML data.
View 2 Replies
View Related
May 31, 2013
I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,
View 1 Replies
View Related
Feb 25, 2008
I have a front page with buttons linking to other spreadsheets, e.g
{ button } Stock Sheet .xls (???)
{ button } Sales Sheet .xls (???)
In the brackets I wanted to display the last updated date of the said spreadsheets, so that a user can see if it has already been updated. Is there a way of retrieving the last updated date for a spreadsheet, and dispaying it (i.e is it held against a variable or named item)
View 9 Replies
View Related
Feb 11, 2013
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.
View 9 Replies
View Related
Aug 26, 2008
I want to create a communication excel sheet. I want it in such a way that if any one of the team member make update and save the file, then the whole team should receive an email that the file has been updated.
View 9 Replies
View Related
Jan 17, 2013
I have an excel report that is generated in another program every week. The report is not sorted by date and it may have 100 rows of data one week and 200 rows of data another week. My excel file has formulas that refer to this original report. I need those formulas to update first, then I need the information to automatically sort by date (column D in my excel file). I have tried to setup a script to run, but it's not working. I will attach the file and the script.
Attached File: Sample w VBA.xls‎
View 1 Replies
View Related
Nov 7, 2008
I want Cell B1 to display the MM/DD/YYYY of when Cell A1 was updated.
so when I update A1, B1 is automatically updated with today's date.
Is this possible?
View 8 Replies
View Related
Feb 12, 2008
I have a spreadsheet which is linked to several password protected spreadsheets. I have a macro which updates the links but I would like the macro to include the date that the links were last updated. Is there a code I can insert into the macro which will display the date the links were last updated?
View 4 Replies
View Related
Apr 21, 2013
I have a Excel Spread Sheet which has been created, there will be 4 people in 4 different locations who will have access to this spreadsheet and will need to update/amend it through "Dropbox", my question/problem is, how can I link the 4 spreadsheets where information will be updated by one person, but will update all 4 sheets at the same time.
Is this possible and how do I go about it?
View 4 Replies
View Related
Mar 11, 2008
I am trying to record the date with time on a pre set cell of a row, in which the cell will show the most current updated date/time once any change is made within the same row, regardless which cell on the row. Is there any code for VBA you can suggest?
View 14 Replies
View Related
Jul 30, 2008
I am trying to add some functionality to a spreadsheet that I manage. Basically i have a small team, and each week I ask them to update a spreadsheet to show what they have been working on in that week. In order to monitor who is going in and updating their entry I would like to add a column that shows me when the last update was for each team member. I have seen this example but it only returns a date based on one cell as opposed to the whole row (or range of cells)
Auto populate the date and time a cell was updated. I have attached an example.
View 2 Replies
View Related
