|
Add 10 minutes to a Date
I have a procedure that imports data from another workbook. I want ensure
that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
RyanH,
Try this function: ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + .00695 -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''Yes'' below. "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
Ther are two solutions
1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
I am getting an error: Automation Error. Can the BuiltinDocumentProperties
only be used with ThisWorkbook? I can't get it to work with the workbook wbkDataSource. If wbkDataSource.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) < Date Then 'do something End If Any ideas? -- Cheers, Ryan "Joel" wrote: Ther are two solutions 1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
The error also occurs with tthisWorkbook. This is a bug with collection
method. Not all collections can be address directly by name. Here is a work around Savetime = "" For i = 1 To ThisWorkbook.BuiltinDocumentProperties.Count PName = ThisWorkbook.BuiltinDocumentProperties.Item(i).Nam e If PName = "Last save time" Then Savetime = ThisWorkbook.BuiltinDocumentProperties.Item(i).Val ue Exit For End If Next i For i = 1 To wbkDataSource.BuiltinDocumentProperties.Count PName = wbkDataSource.BuiltinDocumentProperties.Item(i).Na me If PName = "Last save time" Then Savetime = wbkDataSource.BuiltinDocumentProperties.Item(i).Va lue Exit For End If Next i "RyanH" wrote: I am getting an error: Automation Error. Can the BuiltinDocumentProperties only be used with ThisWorkbook? I can't get it to work with the workbook wbkDataSource. If wbkDataSource.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) < Date Then 'do something End If Any ideas? -- Cheers, Ryan "Joel" wrote: Ther are two solutions 1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
I don't get an error when I use this line:
SaveTime = ThisWorkbook.BuiltinDocumentProperties.Item(12).Va lue But I do get the same error with the "work around code" you gave me and by using ActiveWorkbook. Error description: "Automation Error" SaveTime = wbkDataSource.BuiltinDocumentProperties.Item(12).V alue SaveTime = ActiveWorkbook.BuiltinDocumentProperties.Item(12). Value Any idea why? -- Cheers, Ryan "Joel" wrote: The error also occurs with tthisWorkbook. This is a bug with collection method. Not all collections can be address directly by name. Here is a work around Savetime = "" For i = 1 To ThisWorkbook.BuiltinDocumentProperties.Count PName = ThisWorkbook.BuiltinDocumentProperties.Item(i).Nam e If PName = "Last save time" Then Savetime = ThisWorkbook.BuiltinDocumentProperties.Item(i).Val ue Exit For End If Next i For i = 1 To wbkDataSource.BuiltinDocumentProperties.Count PName = wbkDataSource.BuiltinDocumentProperties.Item(i).Na me If PName = "Last save time" Then Savetime = wbkDataSource.BuiltinDocumentProperties.Item(i).Va lue Exit For End If Next i "RyanH" wrote: I am getting an error: Automation Error. Can the BuiltinDocumentProperties only be used with ThisWorkbook? I can't get it to work with the workbook wbkDataSource. If wbkDataSource.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) < Date Then 'do something End If Any ideas? -- Cheers, Ryan "Joel" wrote: Ther are two solutions 1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
There is no difference between our two methods i the variable i is a number
between 1 and the maximum count. If the strings are matching maybe that is the reason try replacing this statement If UCase(PName) = UCase("Last save time") Then The function will not work with your original string because of the capitalization. "RyanH" wrote: I don't get an error when I use this line: SaveTime = ThisWorkbook.BuiltinDocumentProperties.Item(12).Va lue But I do get the same error with the "work around code" you gave me and by using ActiveWorkbook. Error description: "Automation Error" SaveTime = wbkDataSource.BuiltinDocumentProperties.Item(12).V alue SaveTime = ActiveWorkbook.BuiltinDocumentProperties.Item(12). Value Any idea why? -- Cheers, Ryan "Joel" wrote: The error also occurs with tthisWorkbook. This is a bug with collection method. Not all collections can be address directly by name. Here is a work around Savetime = "" For i = 1 To ThisWorkbook.BuiltinDocumentProperties.Count PName = ThisWorkbook.BuiltinDocumentProperties.Item(i).Nam e If PName = "Last save time" Then Savetime = ThisWorkbook.BuiltinDocumentProperties.Item(i).Val ue Exit For End If Next i For i = 1 To wbkDataSource.BuiltinDocumentProperties.Count PName = wbkDataSource.BuiltinDocumentProperties.Item(i).Na me If PName = "Last save time" Then Savetime = wbkDataSource.BuiltinDocumentProperties.Item(i).Va lue Exit For End If Next i "RyanH" wrote: I am getting an error: Automation Error. Can the BuiltinDocumentProperties only be used with ThisWorkbook? I can't get it to work with the workbook wbkDataSource. If wbkDataSource.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) < Date Then 'do something End If Any ideas? -- Cheers, Ryan "Joel" wrote: Ther are two solutions 1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
Add 10 minutes to a Date
Hey Joel,
I got this code from Per Jessen. It works great. I don't understand why Microsoft would only allow you to use ThisWorkbook and not ActiveWorkbook or an Object Variable. If FileDateTime(wbkDataSource.FullName) + TimeSerial(0, 10, 0) < Date Then 'do something End If -- Cheers, Ryan "Joel" wrote: There is no difference between our two methods i the variable i is a number between 1 and the maximum count. If the strings are matching maybe that is the reason try replacing this statement If UCase(PName) = UCase("Last save time") Then The function will not work with your original string because of the capitalization. "RyanH" wrote: I don't get an error when I use this line: SaveTime = ThisWorkbook.BuiltinDocumentProperties.Item(12).Va lue But I do get the same error with the "work around code" you gave me and by using ActiveWorkbook. Error description: "Automation Error" SaveTime = wbkDataSource.BuiltinDocumentProperties.Item(12).V alue SaveTime = ActiveWorkbook.BuiltinDocumentProperties.Item(12). Value Any idea why? -- Cheers, Ryan "Joel" wrote: The error also occurs with tthisWorkbook. This is a bug with collection method. Not all collections can be address directly by name. Here is a work around Savetime = "" For i = 1 To ThisWorkbook.BuiltinDocumentProperties.Count PName = ThisWorkbook.BuiltinDocumentProperties.Item(i).Nam e If PName = "Last save time" Then Savetime = ThisWorkbook.BuiltinDocumentProperties.Item(i).Val ue Exit For End If Next i For i = 1 To wbkDataSource.BuiltinDocumentProperties.Count PName = wbkDataSource.BuiltinDocumentProperties.Item(i).Na me If PName = "Last save time" Then Savetime = wbkDataSource.BuiltinDocumentProperties.Item(i).Va lue Exit For End If Next i "RyanH" wrote: I am getting an error: Automation Error. Can the BuiltinDocumentProperties only be used with ThisWorkbook? I can't get it to work with the workbook wbkDataSource. If wbkDataSource.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) < Date Then 'do something End If Any ideas? -- Cheers, Ryan "Joel" wrote: Ther are two solutions 1) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 1/(24 * 6) 2) ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + TimeSerial(0, 10, 0) "RyanH" wrote: I have a procedure that imports data from another workbook. I want ensure that the source workbook has been updated in the last 10 minutes before I do the import. How can I do this? Here is an example: ' make sure macola file has been updated in the last 10 mins If ThisWorkbook.BuiltinDocumentProperties("Last Save Time") + 10 minutes < Date Then Call UpdateStatusOnOpen(ThisWorkbook) End If -- Cheers, Ryan |
| All times are GMT +1. The time now is 12:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com