?
Solved

Excel VBA - Current Date/Time

Posted on 2011-03-23
10
Medium Priority
?
785 Views
Last Modified: 2012-05-11
Please can someone advise if it's possible to obtain the current date/time from a file server using VBA or filesystem
0
Comment
Question by:Damozz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 35202457
This should work; I got it here:
http://www.officekb.com/Uwe/Forum.aspx/excel-prog/33269/Shell-Command-Net-Time-Cannot-create-text-file
Sub TimeCheck()

   Shell ("net time \\server /set /yes")

End Sub

Open in new window

0
 
LVL 2

Expert Comment

by:gggoodwin
ID: 35219336
Please explain what you mean by "obtain" as your meaning could affect the answer.
0
 

Author Comment

by:Damozz
ID: 35225053
By obtain, just mean to return the current date/time from the server and enter that value into a cell within and excel spreadsheet.  I have tried using this (just on a laptop at the moment) but it doesn't seem to return the correct date/time and each time I call the function I get very different dates, although the above does work correctly when using it in the command prompt, so is there something else I need to consider when using this in Excel/VBA.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:gggoodwin
ID: 35226354
Good. I thought thought that's what you meant by obtain. I assume you modified the VBA "function" above to actually be a VBA function that returns a value, to remove /set /yes and to replace server with an actual computer's name. Could you please include your code with your next response? Also, which Windows version are you using?
0
 

Author Comment

by:Damozz
ID: 35226383
Function currentTime()

currentTime = Shell("NET TIME \\laptophome")

End Function

Using Windows 7

Thanks
0
 
LVL 2

Accepted Solution

by:
gggoodwin earned 2000 total points
ID: 35226846
Aha! Light dawns over (my) marble head! The problem is that we want TimeCheck to return the result of the net time command and instead it is returning the process ID that the Shell ran!

My apologies, but I have a date with the wife in ten minutes and can't get any further into this until later. At least we know why the dates are screwy - Excel's just trying to make a date out of a process ID!
0
 

Author Comment

by:Damozz
ID: 35226999
OK no problem.  That would explaing why I am getting years between the 1920s and 1950s..If you have a solution you can pass on when more convenient that would be much appreciated...

Many thanks
0
 

Author Comment

by:Damozz
ID: 35227037
If I were certain there wouldn't be an security in place on the PCs that were going to run this I could use the set parameter to update the PC and then use the Now() function to get the updated date, but this would be reliable, if restrictions are in place.
0
 
LVL 2

Expert Comment

by:gggoodwin
ID: 35228417
I'm back!
I did something like this a lloonngg time ago, but it was using dir, not net time. I built a batch file to run the dir command, directing its output to a text file that I then read it in to get the info I was after. A key consideration from Shell help is "By default, the Shell function runs other programs asynchronously. This means that a program started with Shell might not finish executing before the statements following the Shell function are executed." Consequently, one needs to use a "semaphore file" and DoEvent loop to synchronize batch file completion with VBA's execution. The files need were all created and deleted in the tmp folder, using the tmp system variable to locate them, so this handled any security restrictions. The exact code and batch file I used are not with me now. I can recreate I'm sure but not until Thursday or later this week. I hope this is enough to get you going.

Also, it would be much easier if one could simply use the Process ID returned by Shell("NET TIME \\laptophome > %TMP%\timetest.txt") and go into a DoEvent loop on the process's completion, but I've neither seen nor done that in VBA before. Perhaps someone else who has can jump in?
0
 
LVL 2

Expert Comment

by:gggoodwin
ID: 35228447
Look what I just found!
http://www.daniweb.com/software-development/visual-basic-4-5-6/threads/151529
This might be just what you need.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question