How do you declare api function in vbs? 1

[shar]

Is there a way to declare api functions in a vbs file. I have been declaring api function such as:
Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hkey As Long, ByVal sSubkey As String, ByVal lOptions As Long, ByVal lSAMDesired As Long, hResult As Long)
in MS Access modules and using them. But in vbs files I get an error message when using the same declaration statement.

What is it that I am missing?

Thanks in advance.
Shar

 

[LPlates]

The API is..

Public Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
ByVal ulOptions As Long, _
ByVal samDesired As Long, _
phkResult As Long) As Long

If you use it in a form change Public to Private, but if you want access to it from multiple forms place it in a module and leave it public.

Hope it helps.

 

[strongm]

Without going into the detailed reasons why, I'm afraid the bad news is that you can't make API calls from VBScript.

One solution to this is to wrap the API calls in a VB class, and then use the class from VBScript

 

[shar]

Thanks to LPlates & strongm replies.

LPlates,
I am not using a form, just a plain vbs file for users to double click to run.

strongm,
How do you "wrap the API calls in a VB class, and then use the class from VBScript"?

Here is the detail of how and why I want to use this:

My company is upgrading from Office 97 to 2003. Due to number of w/s involved, the the process happens over a period of time, i.e. users will be using Access 97 and 2003 at the same time to access same back-end data. The code that works in MS Access reads the w/s registry, detects the latest version of MS Access installed. The calling function will copy the correct front-end db file to user's w/s based on the value returned by CheckOfficeVersion() function (see below). I have to accomplish this from a vbs file, before user opens the front-end db file.

Here is the MS Access module that accomplished this objective:

(This code was put together from various FAQ & posts in the Tek-Tips forums.)

'****** start code *****
Option Compare Database
Option Explicit

Private Type FILETIME
lLowDateTime As Long
lHighDateTime As Long
End Type ' end FILETIME Type declaration

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hkey As Long) As Long
Private Declare Function RegEnumKeyEx Lib "advapi32.dll" Alias "RegEnumKeyExA" (ByVal hkey As Long, ByVal lIndex As Long, ByVal sName As String, lName As Long, ByVal lReserved As Long, ByVal sClass As String, lClass As Long, ftLastWriteTime As FILETIME) As Long
Private Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hkey As Long, ByVal sSubkey As String, ByVal lOptions As Long, ByVal lSAMDesired As Long, hResult As Long) As Long

Function CheckOfficeVersion() As String
Dim keyname As String 'receives name of each subkey
Dim keynamein As String 'key name that goes in the function
Dim keylen As Long 'length of keyname
Dim classname As String 'receives class of each subkey
Dim classlen As Long 'length of classname
Dim lastwrite As FILETIME 'receives last-write-to time, but we ignore it here
Dim hkey As Long 'handle to the HKEY_LOCAL_MACHINE\Software key
Dim index As Long 'counter variable for index
Dim retval As Long 'function's return value
Dim k, offver(10) As Integer
Const KEY_ENUMERATE_SUB_KEYS = &H8
Const NOT_USED = 0
Const HKEY_LOCAL_MACHINE = &H80000002

keynamein = "SOFTWARE\Microsoft\Office"
retval = RegOpenKeyEx(HKEY_LOCAL_MACHINE, keynamein, NOT_USED, KEY_ENUMERATE_SUB_KEYS, hkey)

If retval <> 0 Then
'Office not installed, get out
CheckOfficeVersion = &quot;None&quot;
End ' terminate the program
End If

index = 0 ' initial index value
While retval = 0 ' while we keep having success (retval equals 0 from the above API call)
keyname = Space(255)
classname = Space(255) ' make room in string buffers
keylen = 255
classlen = 255 ' identify the allocated space

retval = RegEnumKeyEx(hkey, index, keyname, keylen, ByVal 0, classname, classlen, lastwrite)
If retval = 0 Then ' only display info if another subkey was found
keyname = Left(keyname, keylen) ' trim off the excess space
offver(index) = val(keyname)
End If
index = index + 1 ' increment the index counter
Wend ' end the loop
retval = RegCloseKey(hkey)

Call SortArray(offver) 'sort decending

'now check Access versions on the w/s
For index = 0 To 10
keynamein = &quot;SOFTWARE\Microsoft\Office&quot; & &quot;\&quot; & offver(index) & &quot;\Access&quot;
retval = RegOpenKeyEx(HKEY_LOCAL_MACHINE, keynamein, NOT_USED, KEY_ENUMERATE_SUB_KEYS, hkey)

If retval = 0 Then
'found index version of Access, use it.
retval = RegCloseKey(hkey)
Exit For
End If
Next index

Select Case index
Case Is >= 9 'Access 2000 (9), XP (10), 2003 (11)
CheckOfficeVersion = &quot;New&quot;
Case Else
CheckOfficeVersion = &quot;Old&quot;
End Select

End Function

Private Sub SortArray(ArrayIn As Variant)
Dim arFlg As Variant
Dim i, arID As Integer
Dim k As Long
Dim arTmp As Variant
Dim AllSortedflg, Iterflg As Boolean

AllSortedflg = False
Iterflg = True

k = UBound(ArrayIn)
Do While Not AllSortedflg
For i = 0 To k - 1
If ArrayIn(i) < ArrayIn(i + 1) Then 'switch them
arTmp = ArrayIn(i)
ArrayIn(i) = ArrayIn(i + 1)
ArrayIn(i + 1) = arTmp
Iterflg = False
End If
Next i
If Iterflg Then
AllSortedflg = True
End If
Iterflg = True
Loop
End Sub
'**** end code *****

How can I get a vbs file to do the same?

 

[Hypetia]

I don't understand why do you want to do in such a hard way.
If you just want to check the MS Access version, try the following VBS code.
___
[tt]
Dim MSAccess, iVer
Set MSAccess = CreateObject(&quot;Access.Application&quot
Msgbox MSAccess.Version

iVer = CInt(MSAccess.Version)
If iVer >= 9 Then 'Access 2000 (9), XP (10), 2003 (11)
Msgbox &quot;New version&quot;
Else
Msgbox &quot;Old version&quot;
End If[/tt]
___

On the other hand if you want to read and write other registry keys/values, then you should consider using Microsoft Windows Object Model Library (WSH) which already encapsulates some useful API functions to be used in aiding script-based Windows administration.

For example, the following small VBS code uses WSH to retrieve the path of MS Access executable.
___
[tt]
Dim WSH
Set WSH = CreateObject(&quot;WScript.Shell&quot
Msgbox WSH.RegRead(&quot;HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\MSACCESS.EXE\&quot[/tt]

 

[shar]

Hypetia
I tried first set of code in a vbs file and get an error: Line 3, object doesn't support this property. In an Access module, the error says &quot;version&quot; method or property is not supported.

Thanks.
shar

 

[Hypetia]

I don't know which Access version do you have because I tested it on Access 2003 running on WinXP and Access XP running on a Win98 and it worked fine.

Maybe older versions of Access Application object do not expose this property(?) But I am not sure about that.

Anyways, here is an alternate VBS code to retrieve the latest supported version of Access from the registry (like your's code). I hope this will work for sure.
___
[tt]
iVer = GetAccessVersion()
If iVer > 0 Then
MsgBox &quot;Found MS Access version &quot; & iVer & &quot;.0.&quot;
Else
MsgBox &quot;MS Access not found.&quot;
End If

Function GetAccessVersion()
Dim WSH, N
Set WSH = CreateObject(&quot;WScript.Shell&quot
On Error Resume Next
For N = 15 To 5 Step -1
Err.Clear
WSH.RegRead &quot;HKLM\SOFTWARE\Microsoft\Office\&quot; & N & &quot;.0\Access\&quot;
If Err = 0 Then Exit For
Next
If Err = 0 Then GetAccessVersion = N
End Function[/tt]

 

[shar]

Hypetia,

Thank you very much. The vbs code works like a charm. You earned your star.

PS: The MS Access version I tried the code on was 97.