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
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
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
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
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
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
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
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
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
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
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
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
|
TRACKER
