Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Determine Last Saved Date Of Workbook


I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?

Private Sub Worksheet_Activate()
If application.worksheet.lastsaved = msolastweek Then
range("RANGE").clearcontents
End If
End Sub


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Creating A Log That Logs The Last User That Saved The Workbook Including The Date And Time
Is there any way of creating a log that logs the last user that saved the workbook including the date and time. I would like the log to be a sheet hidden in the workbook. Not many people will be saving the workbook.

View Replies!   View Related
Determine Date System Of Workbook In Which Macro Resides
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:

If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If

But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.

View Replies!   View Related
Save Column J Of WorkBook When WorkBook Is Not Saved.
Need VBA code to save column J of open workbook called LmbcAcctsPayable.xls, Sheet1, to same workbook & sheet called LmbcAcctsPayable.xls, Sheet1, when save is not selected.

Reason: The workbook is usually not saved because it is more of a templet with names in column A. Column J is updated with a date when a row is used so I need the column J to copy automatically to the blank templet when exiting without saving (using ThisWorkbook.Save = True in Auto_Close). That way, rows not being used for a long period of time can be periodically eleminated.

View Replies!   View Related
Execute When Workbook Saved
I would like to execute a sub when the workbook is saved, what is the procedure for this.

View Replies!   View Related
Macro To Show That Workbook Has Been Saved
I have a workbook that I constantly am improving with new macros and VB routines. I sometimes have two versions available for use at the same time. I would like to create a macro or routine that will tell me if the workbook has been saved. A simple cell color change to indicate I've saved would be fine. Also, if I DO save the file and the cell color changes to indicate I have saved, I would like the cell color to change back if any change is made to the workbook. This way I will always know if I need to save or not to keep latest changes.

View Replies!   View Related
Conflicting Saved Changes In Shared Workbook
I've designed a performance indicator (PI) recorder for up to 12 users, it has a userform allowing the users to input their data and a button to save the data and unload the user form. the data is saved using the following

Dim NextRow As Integer
On Error Resume Next 'skips line if workbook is not open
ActiveWorkbook. Sheets("DATA").Activate 'sets the "data" worksheets as active
NextRow = Worksheets("DATA"). Range("A" & Rows.Count).End(xlUp).Row + 1 ' finds the next empty row
With Worksheets("DATA").Range("A" & NextRow) 'inserts the data from the user form
there follows a series of offsets to slot the data into the correct column,
then this code to save the workbook
ActiveWorkbook.Sheets("reports").Activate
Application.DisplayAlerts = False ' suppress overwrite warning message
ActiveWorkbook.SaveAs "MY PATH"
CreateBackup=False
Application.DisplayAlerts = True
MsgBox "Spread sheet Saved"

The problem I'm having is that when the user then closes the excel application there is a conflict between the users 'saves' i.e. one ultimately will overwrite the other. is there a way round this ?? (this is in EXCEL 2000)

View Replies!   View Related
Paste Worksheet To Exisiting Saved Workbook
I have one workbook named "Data Master" with numerous worksheets... all sheets are named with the following 6 digit format, eg. 005001, 005024, 005133, 005456, 005763, etc.

I have another directory with files each beginning with the particular 6 digit cost centre number.

I was hoping to have a macro that will... for each worksheet in "Data Master" check a specified directory containing the cost centre files, for the file beginning with the relevant worksheet's 6 digit cost centre number... and simply paste a copy of the worksheet in "Data Master" to the end of the existing sheets in the cost centre file.

View Replies!   View Related
Unprotect A Workbook With VBA And Re-protect It When Saved
I want the excel spreadsheet to "BE Protected" in standard form, and only be "Unprotected" when macros are enabled -- BUT DONT JUMP TO CONCLUSIONS, hear me out, it gets deeper than that

If the user does not enable macros, I want the book to be protected. If they do enable macros, it becomes unprotected.... (On load is good enough, no need to be real-time)..... But, they can save the book, so it would no longer be protected , which is where my question comes in ...

I could use the -unprotect "password"- on load when macros are enabled but this will only work if the spreadsheet was originally protected and is never saved again. This is a problem because this workbook will be given to others and will be saved time and time again.

So, I had a vision :-) .... function 'before save' , protect the sheet using -protect "password"- , allow the sheet to save, and then unprotect back using 'unprotect "password"' so you can continue using - BUT, the problem with this is now I have created an endless loop in theory, because after it unprotects it would want to re-save again because it changed


View Replies!   View Related
Delete All Hidden Worksheets When Workbook Saved
I have working code to unhide specific worksheets from a large number of hidden worksheets and would like to be able to delete all the hidden sheets when the workbook is saved or maybe have a button to delete all the hidden (un-needed) worksheets beside the selection drop-down menu, so that only the selected sheets are saved.

View Replies!   View Related
Last Date Saved Formula
Is there a formula that will get the date & time the workbook was last saved? I am using an earlier posted macro now but would like for the information to be visible constantly in a certain cell. Possible?
Macro used:
Sub WhenSaved()
MsgBox "This file was last saved on " & Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mmmm d, yyyy") & ".", 64, "Date of most recent save:"
End Sub

View Replies!   View Related
Last Date Saved Code
does anyone have a simnple code that when entered in a cell only returns the last date the file was saved? better yet, could it somehow be specific based on a modification to a specific worksheet modified?

example: i have 8 worksheets. how can i show when one worksheet is updated, say November 27th 2006, and another worksheet on November 28th 2006?

View Replies!   View Related
Show UserForm If Template Saved As Workbook .xls
I have created a template with a form. Now I'm using (the code is in ThisWorkbook)

Sub Workbook_Open()
frmMyForm.show
End Sub

This runs the form also when opening the template or a saved workbook based on the template. I want the form to run only when creating a new workbook based on the template. How do I do?

View Replies!   View Related
Last Saved By Time & Date In Cell
I'm looking for a way show the username of the person who last saved the spreadsheet, and the date & time it was saved. I would like to be able to ouput this data in certain cells on the worksheet. I have tried using something like:

View Replies!   View Related
Open File With Last/Lastest Saved Date
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 Replies!   View Related
Userform - Insert File's Last Saved Date
if there is an easy way of showing the active file "Last Saved Date" on a Userform and have it show automatically when the Userform is opened? The Scenario is:

I have a "Main" userform screen and I want to have a text line which says, "File last updated on xx/yy/zzzz ". This must show up automatically when the "main" userform is opened.

View Replies!   View Related
Send Last Saved Date & Time To Cell
I have a macro that returns the time the sheet was last saved:

Function LSDate()
LSDate = Application.Caller.Parent.Parent. _
BuiltinDocumentProperties("Last Save Time").Value
End Function

Unfortuantely, the cell doesn't update without recalculating the cell. Is there a way to get the cell to update automatically every time the sheet is saved?

View Replies!   View Related
Find Files Based On Date Saved / Created
I am about to write a macro and I want it to identify, from the C: drive, any .csv files that were created / saved today. I have managed a pure list before and I have managed to distinguish by filename, but selection by date would be exctremely useful.

View Replies!   View Related
Create Workbooks From Sheets & Append Current Date To Saved Names
I have a workbook with multiple worksheets. First 4 tabs are the standard tabs and rest of the tabs are created based ona macro with the unique names. Now i want to create the separate workbook for each tab by its name and , date and time stamp in a C directory.

View Replies!   View Related
Determine If Workbook Is Open
If I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.

View Replies!   View Related
Determine How Workbook Was Opened
Is it possible to write code that can identify how a workbook has been opened i.e. has the workbook been opened by another workbook or has it received a double click event on the workbook itself.

If it is possible I would like to place the code in the open event of the workbook that is being opened.

View Replies!   View Related
Determine If Workbook Open
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.

If Windows(vCriteria & "ISPR.XLS"). = True Then
MsgBox "true", vbCritical, "Ferguson Enterprises, Inc."
Else
MsgBox "false", vbCritical, "Ferguson Enterprises, Inc."
End If

thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?

View Replies!   View Related
Determine Last User Of Workbook
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user

Function LastUser(path As String)
Dim text As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)
Open path For Binary As #1
text = Space(LOF(1))
Get 1, , text
Close #1
j = InStr(1, text, strflag2)
i = InStrRev(text, strFlag1, j) + Len(strFlag1)
LastUser = Mid(text, i, j - i)
End Function

Above code taken from Determine Last User

View Replies!   View Related
Determine If A File IS A Workbook Before Opening
Is there anyway I can test / inspect a file before attempting to open it to check that it is indeed an Excel workbook?

I don't want to do it by file extension as that can / will be changed, but rather wondered if there was anyway to examine the ACTUAL file?

I'm wondering if all Excel workbook files start with a particular section of data or anything?

View Replies!   View Related
Determine If Shared Workbook Open
Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -

A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box

B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.

Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.

Function IsFileOpened(StrFilePath As String) As Integer
Dim FileNum As Integer
'First check filepath exists
If Len(Dir(StrFilePath)) > 0 Then
FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
If Err.Number <> 0 Then
IsFileOpened = 1 'File open
Else
IsFileOpened = 0 'File Closed
End If
Close FileNum
Else
IsFileOpened = 2 'File not found
End If
End Function

View Replies!   View Related
Determine If Workbook Is Template Or .xls
I have an excel template spreadsheet which auto loads the following when i open it:

Userform1.Show
Usreform2.Show
Application.Dialogs(xlDialogSaveAs).Show

I enter data into the userforms and save as a normal .xls file.

If i then open the .xls the auto open kicks in and opens the userforms and xlDialogSaveAs again.

How do i just get rid of all macros or vba code when i save as a .xls file so nothing auto loads.

View Replies!   View Related
Speed- How Do I Determine What Is Causing My Workbook To Be Slow
I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?

Sample Formulas: ...

View Replies!   View Related
Function/Formula To Determine If Workbook Open
how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.

All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.

Function IsWorkBookOpen(ByVal name As String) As Boolean
Dim wBook As Workbook
Dim isOpen As Boolean

wBook = Workbooks(name)

If wBook Is Nothing Then
isOpen = False
Else
isOpen = True
End If

'I can't reutrn the value of is open?
Return isOpen

End Function

I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?

View Replies!   View Related
Determine Month Of Date
how to put this in

If the date in C3 = January 31 then do something.

I don't know how to put the date part in code.

View Replies!   View Related
Determine Weekday Of Date
The code is ok until I hit a month with 4weeks in it and days left over.
The code puts a week total in place of a Sunday, but as some months end before a Sunday appears the code just builds a Month end total sheet.

What I need in this case is a Week total even if there is no Sunday before it builds the Month end Total.

I hope i'm being clear

Sub NewSheets()
Dim Dte As Date, Dy As Date
Dim i As Long, j As Long, Dys As Long
Dim CountWeek As Boolean
Dim Shts As Long...

View Replies!   View Related
Determine If Date Is Between Two Dates
I have a formula that tries to determine if a date is between two dates. The formula I have is =IF(AND(A1>=A2,A1<=A3),"yes","no")

a1 = 7/31/2009
a2 = 8/1/2008
a3 = 3/31/2010

If the date is between those two dates then "yes", if not then "no". I tried this formula a couple times and it works when a3 is 3/31/2010 but when I try 3/31/2009 for example it does not work. So it seems like there is something wrong with the second date I am referencing.

View Replies!   View Related
Determine Last Modified Date
I have a pivot table for which I load data from a .csv file. Is it possible to include a timestamp on when the .csv file was last loaded. If I do it above the actual pivot table, it just shows when the pivot table was refreshed, but not when the .csv file was last loaded.

View Replies!   View Related
Tip To Show 'last Saved' Date On Worksheet
how I can have the latest date a file was saved showing on the worksheet? For example, I open a worksheet that was last saved on Sept 22nd, and after updating it today, I save it and want the date to show today's date?

I'm pretty sure I've seen it in some files in my working life, so would appreciate advice on this!


View Replies!   View Related
Determine Date Of Cell In For Each Loop
I have a for/next loop working through a date range. I want to skip a date at the 'next' code line.

1st loop Date = 1/1/2008
miss 2/1/2008
2nd loop Date = 3/1/2008

For Each Date In Rng
Sheet1.copy
Sheet2.copy
Next Date+2

View Replies!   View Related
Determine Current Date In Macro
I'm trying to write a batch file that connects to a database and passes in an SQL query. One of the conditions of this query is the date. I need the batch file to figure out what todays date is and pass that in.

This is what I currently have.

ws_tran_date between to_date( '&datefrom 08:30:00AM', 'DD-MON-YYYY HH:MI:SS AM') and TO_DATE('&dateto 08:30:00am', 'DD-MON-YYYY HH:MI:SS AM')

When I run the batch file, it asks me to input the dates manaully in the format DD-MON-YYYY (eg 7-NOV-2006). I need the batch file to figure this out on it's own.

Is there some function I can call to do this?

View Replies!   View Related
Determine Day Name Of Current Date
I have got a spreadsheet that needs filling in by 3pm everyday. The workbook has a worksheet for each day e.g. Mon to Fri. What I am looking to do is fill the worksheets in for whatever day it is e.g. if it is monday then select monday data from required file and paste into monday worksheet. I was going to create five macros for each day and then select the required one on the day, but rather than doing this is their a way that i could write one macro that runs by checking what day it is? e.g. if monday run monday code...

View Replies!   View Related
Determine Accounting 4-4-5 Month From Date
I am trying to determine if a specific date is what week in a 4-4-5 accounting month. See attached file. Is there a formula that can be used to do this???

View Replies!   View Related
Using Reverse Date Of Birth To Determine Age
I'm trying to use a list of date of births to determine an age output.

However the date of births are all in a simple reverse formate, eg

19790822 would be 22nd August 1979

Ideally, i would take this number and the output would just be the persons age in whole years.

View Replies!   View Related
Determine Days Based On Due Date
I am attempting to create a template to input specific dates. If a student submits an assignment on the due date, I have set up the spreadsheet to conditionally format whether the assignment was late or not. In addition, I have a column for when feedback is to be submitted and whether it was on time or not. My problem is this...

I cannot figure out a formula or a way to determine:
If the student submits the assignment late, when will the feedback be due? For example, if submitted one day late, the instructor has an extra day to submit feedback.Two days late is an extra two days to work on it etc..

View Replies!   View Related
Determine If Calendar Control Date Is Between 2 Dates
I have a user form that has a calendar button. Once clicked it updates a txtbox on my user form. What I would also like is for another txtbox (txtQtrLeave) to be auto populated based on this entry. Below is the code I've tried:

If CalendarEnd.Calendar1.Value > #3/31/2008# And CalendarEnd.Calendar1.Value < #1/6/2008# Then
Qtr = 1
frmAddClient.txtQtrLeave.Value = Qtr

... Qtr 2, 3 etc

It seems to return a value if I have one criteria but it doesn't work once I add the And element. What am I doing wrong (this has been driving me mad all morning)?

View Replies!   View Related
Determine Date Of Nth Specific Day Of Month
See attached spreadsheet. I have a set of Jobs with a set of end dates. A meeting date should be held after the end of each job. I need a formula or macro to determine when the meeting date will be held? The criteria to determine the meeting date is found in rows 12-18. The end dates are set on different days of the weeks and different weeks of the month depending on the region that the job is located in. The meeting dates will be input in colums d, f, h & j.

View Replies!   View Related
Determine If Filtered Data Has Date Visible In 1 Column
I have a database that holds thousands of names and the columns are of trinings that they have completed. I can filter by the criteria of name and of ID. ID number is unique so what I want is to know if Column F has a date in it once filtered. It maybe line 789 but that is all it shows because the filter is on.

View Replies!   View Related
Determine If The Input Date (Yearx) In A Userform Is A Leap Year
I need to determine if the input date (Yearx) in a userform is a leap year. I tried doing this: Leap = Evaluate("MOD(Yearx, 4)") IF Leap = 0 then (show 29 days on my planner).

But no matter what date I put in, it generates "0" as the value for Leap, so indicates that February has 29 days. Obviously I'm not doing this right.

View Replies!   View Related
Determine A Specific Date Based On A Number Of Business Days?
I'm documenting the steps of a process with each step accounting for a specific amount of business days. How do I determine the specific business day based on the number of days it takes to complete a process? For example: Start date then 45 business days from that date the next step begins...then 3 business days from that date the following step begins and so on.

View Replies!   View Related
Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month.

Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10
10/15/2009 01/13/2009

I'm using Excel 2007.

View Replies!   View Related
Changes Could Not Be Saved
I am getting the following error message while trying to save an excel file into server location.

Your changes could not be saved to [filename], but were saved to a temporary document named [number]. Close the existing document, then open the temporary document and save it under a new name.

I am using:
OS: Windows XP Professional with Service Pack 2.
Office 2000
Server: Windows 2003 with Service Pack 1.

View Replies!   View Related
Lock Saved Worksheet
I have code written to auto save the active worksheet when a button on my user form is pressed. What I would like to know is if there is a way to lock the worksheet when the button that triggers the save event is pressed behind the scenes so the end user has a more difficult time editing the sheet. I know they can just resave the document and the protection will be gone, but for minimizing user manipulation.

View Replies!   View Related
Document Not Saved 1004
I have an xls file that has many sheets in.

One of the sheets is a sumamry sheet that get's dulpicated, values pasted, then (the single sheet) exported to a new file. Upon trying to save this file, I keep getting the run time 1004 error message and that my document cannot be saved.

I've struggled for days trying to find out the source to no avail.

What is more frustrating is that when I try to save it (after exiting the macro), it seems to work ok.

I do get the compatability error message (which I don't get on the 'original/parent' workbook) there is a not about format errors, though I'm not sure that's the source of the problem.

View Replies!   View Related
How To Get Last Saved Data Into Sheet
I have an excel sheet which is shared, i need to get the date and time when the sheet is last saved in one Particular cell.

Example: In A1 = 16/082007 09.00am (in whatever format)

View Replies!   View Related
Non-Changed Worksheet Asking To Be Saved
I chose to place this in programming, as all the functions for this workbook are either macros or Internet Links. Nothing is ever changed on the worksheet.

I have a workbook that is fully protected with passwords both for the workbook and the worksheet. When I set up the protection, no check boxes are checked.

I have macros and internet links on the worksheet that can be selected, but none of them affect the worksheet.

As a matter of fact, I can open the workbook and close it with only "Application.Quit" in a macro and a message box comes up asking if I want to save the changes or not. If I close it with the upper right corner RED X, it still asks the question.

I have no "On Open Macros" or Links of any kind.

I have other workbooks that this does not happen on.

View Replies!   View Related
File Cannot Be Saved In This Format
I have a file with code attached and when I try to save it I am told that the file cannot be saved in this format and that I must unprotect the code before saving.

I have no OnEvent macros in the code.

Does anyone have a solution for this problem. The code must be protected, non-protection is not an option.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved