Damozz
asked on
Excel VBA - Current Date/Time
Please can someone advise if it's possible to obtain the current date/time from a file server using VBA or filesystem
Please explain what you mean by "obtain" as your meaning could affect the answer.
ASKER
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.
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?
ASKER
Function currentTime()
currentTime = Shell("NET TIME \\laptophome")
End Function
Using Windows 7
Thanks
currentTime = Shell("NET TIME \\laptophome")
End Function
Using Windows 7
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Many thanks
ASKER
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.
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?
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?
Look what I just found!
http://www.daniweb.com/sof tware-deve lopment/vi sual-basic -4-5-6/thr eads/15152 9
This might be just what you need.
http://www.daniweb.com/sof
This might be just what you need.
http://www.officekb.com/Uwe/Forum.aspx/excel-prog/33269/Shell-Command-Net-Time-Cannot-create-text-file
Open in new window