Determine Last Saved Date Of Workbook

Sep 7, 2007

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

ADVERTISEMENT

Creating A Log That Logs The Last User That Saved The Workbook Including The Date And Time

Mar 16, 2004

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 9 Replies View Related

Determine Date System Of Workbook In Which Macro Resides

Oct 24, 2007

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 2 Replies View Related

Save Column J Of WorkBook When WorkBook Is Not Saved.

May 27, 2006

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 3 Replies View Related

Execute When Workbook Saved

Oct 15, 2009

I would like to execute a sub when the workbook is saved, what is the procedure for this.

View 3 Replies View Related

Activating New Workbook That Hasn't Been Saved As Yet

Apr 3, 2014

I am busy with code that filters and copies from a "source file" using dialogue to choose criteria than adds a workbook and pastes filtered information. I than go back to "source file" and do second filter. My question is this newly added workbook changed from book1 to book2 etc everytime the user will run the macro. How do I go about activating the workbook for second and third paste without it being saved?

Here is name code:

[B]Workbooks.Open ("C:Documents and Settingsabek276DesktopSource File.xlsx")

Options = InputBox(Prompt:="Scheme Code", Title:="Options")
Options1 = InputBox(Prompt:="Scheme Code", Title:="Options")
Options2 = InputBox(Prompt:="Scheme Code", Title:="Options")

[Code].....

View 7 Replies View Related

Unprotect A Workbook With VBA And Re-protect It When Saved

May 13, 2008

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 12 Replies View Related

Macro To Show That Workbook Has Been Saved

Nov 25, 2009

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 3 Replies View Related

Delete A Range When Workbook Is Saved

Nov 15, 2011

I currently have the code below that closes all but 1 sheet in my workbook called Main

Is there anyway i can clear a range of cells on a couple of sheets within the workbook when save is pressed?

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For a = 1 To Sheets.Count
If Sheets(a).Name "Main" Then Sheets(a).Visible = 2
Next a
End Sub

View 4 Replies View Related

Conflicting Saved Changes In Shared Workbook

Apr 25, 2008

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 2 Replies View Related

Delete All Hidden Worksheets When Workbook Saved

Oct 15, 2008

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 4 Replies View Related

Disable Macro In Workbook Open When Saved

Nov 17, 2011

I have a macro in a workbook template,that when the workbook is open it populates a cell with the date and time such as 111711.507. This becomes the contract number for this workbook.. They complete the form or workbook and save it. The next time they open the saved workbook the macro runs and changes the contract number to a new number. I want to disable the macro on the saved workbook from changing the number.

View 2 Replies View Related

Paste Worksheet To Exisiting Saved Workbook

Jan 8, 2007

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 9 Replies View Related

Show UserForm If Template Saved As Workbook .xls

May 12, 2008

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 3 Replies View Related

Date Format Is Not Saved In CSV

May 5, 2014

I have an excel file in which the date format for a column was in the following format -: 3/14/01 Since I wanted the column to display in 'mm/dd/yyyy' format, I changed the type to -: 03/14/01 and the sheet was saved. I then for a specific purpose converted the xls file to csv and noticed that the date formatting I did is not getting saved. That is, every time I open the csv the date format changes back to 3/14/01.

how to retain the date format in excel? I need to use the date format in mm/dd/yyyy only for the application I'm using.

View 2 Replies View Related

Last Date Saved Code

Nov 29, 2006

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 9 Replies View Related

Last Date Saved Formula

Dec 20, 2006

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 9 Replies View Related

Opening Saved File Also Opens Blank Workbook

May 3, 2013

When I open any saved Excel file the saved file opens along with a blank workbook titled "Book 1". How can I make it so when I open any saved Excel file it only opens the file I want and not a new workbook every time?

View 2 Replies View Related

Last Saved By Time & Date In Cell

Jul 18, 2009

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 2 Replies View Related

Tip To Show 'last Saved' Date On Worksheet

Sep 24, 2008

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 9 Replies View Related

Userform - Insert File's Last Saved Date

Dec 15, 2009

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 3 Replies View Related

Open File With Last/Lastest Saved Date

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

Send Last Saved Date & Time To Cell

Jun 24, 2008

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 2 Replies View Related

Find Files Based On Date Saved / Created

Aug 4, 2006

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 2 Replies View Related

Create Workbooks From Sheets & Append Current Date To Saved Names

Apr 22, 2008

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 5 Replies View Related

Determine If Workbook Is Open

Oct 19, 2008

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 2 Replies View Related

VBA To Determine If Workbook Is Open

Nov 29, 2013

I cannot cut and paste from my working computer to the internet access computer.

This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.

WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.

This is the call to the function:

Code:
WS_exists = Is_Workbook_Open( WB_PCM, WS_PARAMETERS_NAME )

And that code is

Code:
Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean
Dim check_sheet as Worksheet
On Error Resume Next
If( workbook_ref Is Nothing ) Then
Is_Workbook_Open = False
Else
Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error

[code].....

During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.

View 3 Replies View Related

Determine How Workbook Was Opened

Sep 8, 2006

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 5 Replies View Related

Determine Last User Of Workbook

Sep 10, 2007

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 2 Replies View Related

Determine If Workbook Open

Jan 1, 2008

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 2 Replies View Related

Determine If Workbook Is Template Or .xls

Apr 18, 2008

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 3 Replies View Related

Determine If A File IS A Workbook Before Opening

Aug 6, 2009

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 5 Replies View Related

Determine If Shared Workbook Open

Apr 6, 2008

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 2 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy

[code]....

View 4 Replies View Related

Speed- How Do I Determine What Is Causing My Workbook To Be Slow

Apr 16, 2009

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 9 Replies View Related

Function/Formula To Determine If Workbook Open

May 16, 2008

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 3 Replies View Related

Determine Last Modified Date

Oct 1, 2008

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 4 Replies View Related

Determine Month Of Date

Oct 31, 2006

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 5 Replies View Related

Determine Weekday Of Date

Sep 19, 2007

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 3 Replies View Related

Determine Day Name Of Current Date

Oct 23, 2007

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 5 Replies View Related

Determine If Date Is Between Two Dates

Apr 1, 2008

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 3 Replies View Related

ADVERTISEMENT