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


Advertisements:










Track Date And Time A File Is Opened


I found some vba codes in this forum which record the date and time in column A when a file is opened.

Can the codes be modified such that when someone opens the file the second time on the same day, the date and time are recorded in column B?

Attached file for your reference.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Track The Time From 1-NY To 10-Lukket

So if we take first line the defect id is 10. The date entered is 21-08-2007 and the status is 1-NY ( means new)
NExt line the defect ID is still 10 the date is changed to 22-08-2007 and the previous status is was 1-Ny and the new status is 03- under vurdering (to be
evalueted)
Third line the defect ID is still 10. The date is changed to 04-01-2008. The previous status was 03- Under vurdering ( to be evalueted). The new status is 10-lukket.

I want to be able to track the time from 1-NY to 10-Lukket.

Now the even more tricky part. As you can see other defects go over different statuses e.g defect number 1000 here.

The sheet I have is 13000 rows and I have a total of 2300 defects

1021-08-2007 10:491-Ny
1022-08-2007 15:381-Ny03- Under vurdering
1004-01-2008 13:4703- Under vurdering10-Lukket
10029-08-2007 10:051-Ny
10003-09-2007 14:121-Ny04- Afventer tilretning
10012-09-2007 15:2204- Afventer tilretning10-Lukket
100006-02-2008 11:2901-Ny
100012-02-2008 15:0501-Ny04- Afventer tilretning
100015-08-2008 08:5304- Afventer tilretning10-Lukket
100015-08-2008 22:1310-Lukket04- Afventer tilretning
100004-02-2009 14:0404- Afventer tilretning10-Lukket


View Replies!   View Related
How Do I Save A File With Date And Time
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 Replies!   View Related
Get Time Application Was Opened
I need to know when excel opened. Is there a way to tell this?

View Replies!   View Related
Save File Name With Date/time Stamp
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

View Replies!   View Related
Track Crew Members Daily Time
I am trying to make a workbook to track crew members daily time.

I would like to be able to track time by Tag # and by W/O # for daily hour totals.

View Replies!   View Related
Calculating Time To Track Work Hours
I can't figure out how to do a proper formula for calculating time. For instance I have one column that says "Time In", the next is "Time Out" and the other is "Lunch Time". My calculation needs to be this:

Find the total hours between the Time In and Time Out and then Minus the lunch time to get total hours worked for the day. The only way I can get this to work is using 24 Hour time format. Is there another way?

View Replies!   View Related
File Crashes When Opened
I run a macro which runs saves the active file to a different location to ensure that I retain the original like so -

If ActiveWorkbook. Name = "KEY_DATA_CFT77" + ".xls" Then
ActiveWorkbook.SaveAs Filename:= _
"H:HOME imcEXCELKEY_DATA_2006KEY_DATA_2006_01KEY_DATA_CFT77_01.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If .................................


View Replies!   View Related
Log Of Who Has Opened File
Is there a way to create an outside log of who has opened an excel file and when?
I am looking to add some accountability to a project.

View Replies!   View Related
Return To Certain Tab Each Time Workbook Is Opened
I have an excel file on a share site which is accessed by several people. The first tab is set up as a home page which directs the person to the relevent part of the work book that they need. My problem is that as the person saves their updates it also saves the location of the workbook they were in. I want it to return to the " homepage" automatically each time it is opened.

View Replies!   View Related
Increment Cell Each Time Workbook Is Opened
Below the code allows me to get the last modified, but I want to get the last time the file was access. How do get the "Last Accessed" Date/Time?

If CurrentTime < ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") Then
MsgBox "Date Moved"

View Replies!   View Related
Open Userform When File Is Opened
I have a Userform in Excel.

My requirement, is whenever I open the Excel file, it should open the userform and should not show the excel file at all. And the form is closed, the excel file should be saved and closed.

Also, I need the minimize button on the form and in the taskbar, it should not display the Excel file, it should display only the Userform.

View Replies!   View Related
Closing A File Opened Via GetOpenFilename
The Macro asks the user to point at the location of a report, it then copies information out of that report and pastes it into a master sheet. The part I am having trouble with is closing the file that data has been copied from.

I have tried different things to close it such as

View Replies!   View Related
Refer To File Opened By User
I am trying to refer to an open file in VBA script.

The script below is asking the user to open a file from which data is copied. I am trying to refer back to this open file but as the file name and path are not mentioned in the script, I am not able to.

myMsg = "Please Select Euromcontact Master DISTOPS File"
Response = MsgBox(myMsg, vbExclamation + vbOKOnly, myTitle)
sFileName = Application. GetOpenFilename
Workbooks.Open Filename:=sFileName
Sheets("CL value").Select
Sheets( Array("CL value", "CL Units", "CLC Value")).Select
Range("N12:N37,S12:Z37").Select
Selection.Copy
Windows("Euromcontact Value File TEMPLATE.xls").Activate
Sheets("CLvalueDIS").Select
Sheets(Array("CLvalueDIS", "CLUnitsDIS", "CLCvalueDIS")).Select
Range("R12").Select
Selection.PasteSpecial Paste:=xlValues
Sheets("CLvalueDIS").Select
Sheets(Array("CLvalueDIS", "CLUnitsDIS", "CLCvalueDIS")).Select
Range("R12:R37").Select
Selection.Copy
Range("N12").Select
Selection.PasteSpecial Paste:=xlValues
Range("R12:R37").Clear
Application.CutCopyMode = False

View Replies!   View Related
Highlight Last Opened File In List
For a project I need to develop excel spreadsheet that will contain two columns. First column will contain list of sale items. Other column will contain hyperlink to the items image as pdf file stored on hard drive somewhere. My requirement is to highlight row containing sale item, whoes pdf file is modified/updated since the last time user has opened the spreadsheet.

View Replies!   View Related
Reject User If File Is Being Opened By Other
how to alert and force second user to quit (i.e. even cannot open the file as "read-only) if the excel file is being opened by other?

View Replies!   View Related
Csv File Different When Opened Manually And Via VBA
Opening it manually the dates in column C are all consistently date numbers. i.e. using the =ISNUMBER() function to test them, they all return True
However when I include the VBA instruction:

View Replies!   View Related
Group All Opened File In 1 Tab Instead If Many Tabs
can group all opened file in 1 excel tab instead if many tabs?
open thru window the file u want...
http://i12.photobucket.com/albums/a209/tancfc/11444.jpg
http://i12.photobucket.com/albums/a209/tancfc/fhfh.jpg

right click on task bar > properties > under Taskbar Appearance, select Group Similar taskbar buttons > is not my answer

View Replies!   View Related
Counting The Number Times A File Is Opened
I have a workbook setup as Read only for reporting purposes. I would like to capture the number of times and if possible, the users that are opening the file....is there a way?

View Replies!   View Related
Copy/paste From File Opened By Macro
I have a macro which opens two different hyperlinks in excel. Opened workbooks have are named like 1.asp and 2.asp. Both contains only one spreedsheet. I need to copy these spreedsheets to the Workbook, from which they are opened.

View Replies!   View Related
Auto Run Macro When File Is Opened
I am using the following code and putting it into a module.

Private Sub Workbook_Open()
MsgBox "Hi, thanks for opening me", vbInformation, "Ozgrid.com"
End Sub

when i open the file and click on allow macros it does not run.

View Replies!   View Related
Corrupt File: Filename.xls Cannot Be Opened
I was working on a tax file yesturday, just a long long list of receipts. and the file is password protected. However, I went to open it today, and I get a message that says: "Filename.xls cannot be opened. the file may be read only, or you may be trying to access a read only location. Or, the server the file is located on may not be responding."

It's on my USB Drive. I've tried copying it to my HD so I can play with a copy, but no dice. When I try that, I get another error message, : Cannot copy Filename.xls. The file or directory is corrupted and unreadable. I've tried the various fixed suggested in the excel help files to no avail. I did a search on the forum, but didn't find anything that seemed to apply or help me. The computer I'm trying to open it on has Office 2003, the one I most recently worked on the file on has office XP. Not sure if this was a source of the problem. Doesn't seem like it should be. Hasn't been in the past. I'm not even getting to the dialogue box asking for my password yet.

View Replies!   View Related
Blank Workbook Opening When File Opened
When I open a saved excel file I am having a problem with one workbook opening(a blank one), then the box saying that the program contains macros, and then the second actual workbook opening. I do not know what I changed somewhere alone the line, how to make just the one workbook I want (the second one) to open?

View Replies!   View Related
Track Changing Date
How can I track date of every last change made in certain cells so my boss can monitor if I am keep working on a project?

View Replies!   View Related
Update A User Name & Usrname() When File Is Opened
I have a cell that displays the user name and would like the cell to update whenever the file is opened and display the user. Below shows what the VBA module code. The cell has this formula "=Usrname()".

Function Usrname() As String
Usrname = Environ("username")
End Function

View Replies!   View Related
Can You Track The Date A Different Field Was Updated?
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 Replies!   View Related
Enable Auto Refresh Of Web Queries When File Opened
I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using web queries.

My problem is that I get a dialogue box each time the spreadsheet is opened: "Enable Automatic Refresh." I need to remotely reboot the PC from time-to-time so I can't click the button.

I have set my security levels to low and that does not help.

View Replies!   View Related
Displaying The Current Opened File's Directory Path?
What is the easiest way to display in a cell the opened file's directory path where the file is stored? I would like to use some sort of formula (instead of the VB route) but I don't know if such a thing exists.

View Replies!   View Related
Toolbar When Work Papers Are Opened From Within The Engagement File Room
I am an IT Consultant and my client is doing a workstation refresh. They are a CPA firm. They use software called ProSystem Fx Engagement as a filing closet sort of thing. This has a toolbar Excel when work papers are opened from within the Engagement file room. In the Engagement toolbar there is an icon which allows the user to check all boxes. I have contacted the vendor and they said they know nothing about this and it is unsupported. I am trying to migrate to the user's new machine (helps save alot of time) but kinda stuck in a rut. I am assuming this maybe is a custom macro? I am not an Excel guy, I do server stuff etc... but trying to please my client, I included a screen shot of the toolbar and highlighted the button he would click to make this function work. This is Excel 2003 w/ SP4, and Windows XP Pro w/ SP3

View Replies!   View Related
Ensure Active Workbook When File Opened By Windows Scheduler
This code opens a UserForm in the Workbook_Open event when the file is opened. After 10 seconds, the message is supposed to go away if the user doesn't click anything, then macros that follow are supposed to run. It is intended to allow a user to stop the macros. This works when you open the file manually (macro security must be low), you'll see a msgbox pop up after the UserForm goes away.

However, when the workbook is opened as a Windows Scheduled Task (Windows/ Control Panel/ Scheduled Tasks), the UserForm does not come to the front of the screen unless a user actually clicks on the window. It just flashes at the bottom indefinitely.

View Replies!   View Related
Automatic Link Update WITHOUT Prompt When File Opened Not Working (XL2003)
Despite setting the "Edit/Links/Startup Prompt/Don't display the alert and update links" option numerous times, my workbook still prompts me to update links every time I open it. The option seems to be set okay (it is preset whenever I go into the "Edit/Links/Startup Prompt" dialog), but it doesn't seem to affect the workbook's startup behaviour. The workbook contains a ComboBox control that is initialized with customer names from another workbook, which is included in the References for this main workbook.

I am using Excel 2003 (from Office Pro 2003) under Windows XP (SP1). I believe this used to work without the prompt when I was working on this app last fall (I'm not sure, as my memory of specific behaviours back that far is fuzzy). However, it has been persistently prompting ever since I started working on this app again this spring/summer. Was there perhaps a bad fix to Office 2003 (that I automatically applied) that broke this feature?

View Replies!   View Related
Date/Time Formula: Pick Up A Date With Time Entry On A Worksheet And Place It Into A TextBox On A UserForm
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the

Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub


"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?

View Replies!   View Related
Update Time And Date When I Update Data Not When I Open A File
I have used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?

View Replies!   View Related
Subtract A Static Date And Time From The Current Date And Time
I have a column of values resulting from subtracting a static date and time from the current date and time.

This means it is constantly updating, which makes it impossible to sort.

All my work depends on sorting those values, though.


View Replies!   View Related
Date/time Macro: Inserts The Current Date And Time In The Selected Cell Regardless Of Where That Cell Is
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?

View Replies!   View Related
Converting Date:Time Text To Date:Time Value
I'm trying to write a VBA code for converting the date/time as text to a date/time value.

The scenario is, I have three different types of text :
ddmmmyy
HH:MM
ddmmmyy:HHMM

I could have the code working fine with the first two types, but not the last one.

View Replies!   View Related
Disable The Vba In A File Being Opened Using Vba
I have excel vba code to open .xls files on my server. Files to open are chosen by the user via a UserForm within my app, it shows them all their files in their directory on my server. How can I open their files (.xls) that they select but DISABLE the VBA from being able to run/execute, if they have any attched to the file?

I now have all code in place to open the file no problem, I just need to know how to disable the VBA part from being "turned on" when the file opens.

View Replies!   View Related
Saving Multiple Workbooks At One Time/file As A Text File
found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?

Sub SaveAsCell()
Dim strName As String

On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName

It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?

Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.

View Replies!   View Related
Format Date & Time To Show Day, Date & Time
I have a sheet with a list of dates in it that I wish to format to show the day as well as date and time. I am trying to do this automatically with a macro, below is the before and the desired after.

Before
Format - dd/mm/yyyy hh:mm
Appearance - 29/03/2009 00:30

After
Format - ddd dd/mm/yyyy hh:mm
Appearance - Sun 29/03/2009 00:30

This works fine when I format the cells manually, but when I try to format them via macro '29/03/2009 00:30' becomes 'Sun 29/03/2009 12:30' which is obviously a totally different time! Has anyone got ay idea why it might be doing this?

View Replies!   View Related
Convert Date & Time As Text To Real Date & Time
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.

View Replies!   View Related
Copy Row If DATE & Time In Cell Is Between Time Span
I have a problem regarding sorting data having date and also time within a single cell.

Example data (I have written it as code to preserve formatting)

A B C D E F

12/5/2008 02:072/5/2008 06:0128804833363
22/5/2008 18:012/5/2008 18:0599271297

Column B is start date and time whereas Column C is end date and time.
My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.


View Replies!   View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends.

eg:

Fri 24-Aug-07 10:52 is the date and time

28:48:00 is the time

If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40

But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)

View Replies!   View Related
Delete Date & Time Cells Based On Time
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too.

I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have.

Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......

View Replies!   View Related
Format Date & Time Cell To Hide Time If 0:00
I have a Excel Dates & Times column where the time is not always used. In these cases the time is 12:00AM. Is there a way to Custom Formats the cell so that the time is only visible if it is not 12:00AM?

View Replies!   View Related
Extract Time From Custom Date And Time Format
I have a file that has the Date and Time combined into one cell. I want to separate the two, and cannot find anywhere on the net to do so!

This is the cells format:
d/mm/yyyy h:mm

Cells look like this:
28/05/2008 12:30

View Replies!   View Related
Copy N Paste Time Date/time Format
I have a cell with both date & time "10/9/09 3:15" This is put in the current cell by formula which indexes two dif. cells, Now I am trying to copy this cell and paste into another book but like to have only date. How can I do that? Each time I try it gives me the time value in the pasted cell and I cannot even format it.

View Replies!   View Related
Calculate Date Differences: Time In & Time Out
I'm trying to devise a formula to produce "days in inventory" based on the following data:

Date In
Date Out (which may be blank if cargo still here)
Todays Date
Days in Whse (which is the formula I can't figure out!)

It needs to work like this, date out - date in, unless date out is null. If date out is null, the result should be calculated based off of todays date - date in.

View Replies!   View Related
Date Function- Open A File, Make An Exact Copy And Save It Under A New File Name
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 Replies!   View Related
Extract Date/Time To Date And Time
In Cell A1 = 01/12/2008 12:00:00 AM

I am trying to extract in cell A2 = 01/12/2008 and A3 = 12:00:00 AM.

But I will end up having a string of numbers each time I try to right (A1,11).

My end result is I will use the values in A3 if between 12am to 5am, assign a value in A4 as "Night Shift"

A1 = 01/12/2008 12:00:00AM
A2 = 01/12/2008
A3 = 12:00:00Am
A4 = if(And(A3>12,A3<5),"Night Shift","Day Shift")

View Replies!   View Related
Remove The Time From A Cell With Date And Time In It?
I have data exported from a defect management tool (that reports all the defects that have been recorded in the defect management system). This data has unique columns for name of originator, defect ID, date originated, priority, etc. The date originated column has the date and time. I want to create a report that tells me how many High, Medium and Low priority defects were created on each date - and I am using a pivot table to do this.

However, because the date column is actually the date and time the defect was reported/recorded, the time element of this is making all the dates unique eg, I have three defects recorded on 29/01/2009, a High priority defect (reported at 29/01/2009 12:23) and two Medium priority defects (reported at 29/01/2009 13:02 and 29/01/2009 13:32). My pivot table is showing each date as a unique date - with a count of 1 against each, whereas I want to see 1 date entry (for 29/01/2009) with a count of 1 against High and 2 against Medium.

I have tried custom formatting the date column to dd/mm/yyyy - which appears to work in the data (but doesn't in the pivot table, because the time stamp is still there, just not visible). I have tried copy and paste special [Values] (on the custom formatted date column) -but the time stamp is still there. I have tried formatting the date column to "General", but this changes the dates to the number format - eg 29/01/2009 12:23 becomes 39842.5166203704.

I have tried inserting a new column and using the =Left function for the first 10 characters of the cell (ie the 29/01/2009), but that gives the first 10 characters of the number form of the date, ie 39842.5166. Basically, without going into every date cell (and I have thousands) and manually deleting all the times, how do I quickly change the date column into just having the date in it (ie how do I strip off the time element)?

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