VBScript files

[Rob.Mills]

I'm new to creating vbs files. I've used VBA quite a lot so I thought it might be easy to start using them. I created this code in a module in a database to test the date modified on a two files, and if they differ then update the destination file. The idea is to test if there's a new copy of the frontend on the server and if so copy it over. It works fine in the database but if I copy it into notepad and save it as a vbs file it creates an error.

Option Explicit


Public Sub sOpenFile()

On Error GoTo ErrorHandler

Dim fso As FileSystemObject
Dim fsoFile As File, fsoFileDest As File
Dim dtmSource As Date
Dim dtmDestination As Date

Set fso = CreateObject("Scripting.FileSystemObject")

Set fsoFile = fso.GetFile("C:\My Documents\Test.txt")

Set fsoFileDest = fso.GetFile("C:\Documents and Settings\Rob\Desktop\Test.txt")

dtmSource = fsoFile.DateLastModified

dtmDestination = fsoFileDest.DateLastModified

If dtmSource > dtmDestination Then

fsoFile.Copy "C:\Documents and Settings\Rob\Desktop\Test.txt", True

End If

ExitProcedure:

Set fsoFile = Nothing
Set fso = Nothing

Exit Sub

ErrorHandler:

Select Case Err.Number

Case 53

fsoFile.Copy "C:\Documents and Settings\Rob\Desktop\Test.txt", True

Case Else

MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbExclamation

End Select

Resume ExitProcedure

End Sub

 

[Rob.Mills]

I put this out on Sunday evening. Maybe I'll have better luck today.

 

[Big Otis]

Here's what i see you might want to check out.



1. With vb script, error handling is not like VBA. Essentially you have to declare a variable, and after each line of code that does something, set the variable = to the err number, then call another sub that handles the errror. Also, each sub must have the declaration ON Error Resume Next at the beginning. This can get fairly lenghty since it obviously adds additional code lines to your script.

3. Nothing is calling your sub routine. Also, vbscript doesnt like the explicit declarations. Try removing Public from in front of your Sub name. Also, all variables in vbscript are variants, therefore you will get errors if you try to specify "as String", etc ....

That's all i have time for right now ...

 

[Rob.Mills]

Ok. I've modified it quite a bit. Learned a lot so far. This successfully tests whether a file has been updated. If so, it copies it over.

The last thing I'm trying to get it to do is open the database. But my code doesn't seem to want to work. Can you take a look, please?

Sub OpenFile()

On Error Resume Next

Set fso=CreateObject("Scripting.FileSystemObject")
Set fsoSource=fso.GetFile("\\rals1002\all\Tax\Outsourcing\CP\FrontEnd\CP_DateStamp.txt")
Set fsoDB=fso.GetFile("\\rals1002\all\Tax\Outsourcing\CP\FrontEnd\CP_fe.mde")
Set fsoDestination=fso.GetFile("C:\Documents and Settings\rmi\My Documents\CP_DateStamp.txt")

If Err.Number<>0 Then

fsoSource.Copy "C:\Documents and Settings\rmi\My Documents\CP_DateStamp.txt", True
fsoDB.Copy "C:\Documents and Settings\rmi\My Documents\CP_fe.mde", True

ElseIf fsoSource.DateLastModified<>fsoDestination.DateLastModified Then

fsoSource.Copy "C:\Documents and Settings\rmi\My Documents\CP_DateStamp.txt", True
fsoDB.Copy "C:\Documents and Settings\rmi\My Documents\CP_fe.mde", True

End If

Set objAccess=CreateObject("Access.Application")
objAccess.Visible=True
objAccess.OpenCurrentDatabase "C:\Documents and Settings\rmi\My Documents\CP_fe.mde"

Set objAccess=Nothing
Set fsoDestination=Nothing
Set fsoDB=Nothing
Set fsoSource=Nothing
Set fso=Nothing

End Sub

 

[Rob.Mills]

I also added at the very top

Call OpenFile()

What this does now is open the database and then close it again. How do I keep it open?

 

[Big Otis]

What is it you want to do with the db? Just have it open for use?

 

[Rob.Mills]

that's right

 

[Big Otis]

When you kill the object refering to the access app, that's probably killing the instance of the app as well.

To do what you are trying i would try the following (not tested ) but you can test ...BTW, this assumes an installation of Access 2K

    dim wshShell2,result,strOfficePath,strDbPath
    strDbPath = GetCurrentPath 'This is a user defined function
    Set wshShell2 = CreateObject("Wscript.Shell")
    strOfficePath =wshshell2.regread("HKLM\Software\Microsoft\Office\9.0\Access\InstallRoot\Path")
    result = wshShell2.Run (chr(34) & strOfficePath & "msaccess.exe" & chr(34) & " " & strDBPath & "\YourDB.mdb /nostartup /x AutoJob",,True)
    Set wshShell2 = Nothing

This code would open the db named "YourDb" with no splash screen and then execute the macro "AutoJob" . You can look up the command line options for Access to see how to modify that to suit your needs ...

Just as a note for more learning, I don't use vbs files myself, except as library files. You might want to check out the documentation on WSF files and their structure. They allow you to have include statements so you can reuse procedures as needed.

 

[Rob.Mills]

Ok. Got that to work. Thanks for the help on that.

One more thing. Do you know if there's a way in vbscript to change the mouse pointer to an hourglass?

 

[Big Otis]

Glad you got something going.

Right off hand, i dont have a way to change the mouse pointer. i have never had the need since my vbscript jobs all run on a schedule and no one is sitting and watching them .....