Overcome Type mismatch: Format error - changing to correct VBS Command to do this action.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Need correct syntax or code to format a number using vbs.

Please note the following code which grabs your default MS Access location by formatting the numeric version using the command Function:

The line that contains the error:  Type mismatch: 'Format'

strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Please note attached file that shows the listed error.

Basically just want to know how I can either created my own Format function in vbs to do the above or what should be the correct command?
I tried FormatNumber instead of Format but that didn't work.  I tried convert string to integer and then FormatNumber(  … but that didn't work.


    Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//./root/default:StdRegProv")
    sKey = "Access.Application\CurVer"
    oRegistry.GetStringValue HKEY_CLASSES_ROOT, sKey, "", sValue
    sAppVersion = Right(sValue, Len(sValue) - InStrRev(sValue, "."))
    Set oRegistry = Nothing


    'Specify the registry trusted locations path for the version of Access used
    strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Format(sAppVersion & "", "##,##0.0") & "\Access\Security\Trusted Locations\Location"

Open in new window

ee-vbs-error.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
VBScript has not overloaded Format() function. It has specialiced Format{DataType} functions. In your case FormatNumber(). The input must be number, otherwise you'll get a type mismatch runtime error. E.g.

Option Explicit

Dim nAppVersion

nAppVersion = 123.345
WScript.Echo FormatNumber(nAppVersion, 1, True, False, True)

Open in new window

But it's of no real use for formatting a version number. Do traditional string fiddling and concatenate the single parts before writing it to the registry.
IT / Software Engineering Consultant
Top Expert 2016
Commented:
Give this a try, I didn't see the needed registry constants defined.  Also added a check to make sure the registry value was found.

    Const HKEY_CLASSES_ROOT 	= &H80000000
    Const HKEY_CURRENT_USER 	= &H80000001
    Const HKEY_LOCAL_MACHINE 	= &H80000002
    Const HKEY_USERS 		= &H80000003
    Const HKEY_CURRENT_CONFIG 	= &H80000005

    Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonate}//./root/default:StdRegProv")
    sKey = "Access.Application\CurVer"
    iReturn = oRegistry.GetStringValue(HKEY_CLASSES_ROOT, sKey, "", sValue)

    If iReturn = 0 Then
        sAppVersion = Right(sValue, Len(sValue) - InStrRev(sValue, "."))
        'Specify the registry trusted locations path for the version of Access used
        strLnKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & FormatNumber(sAppVersion, 1) & "\Access\Security\Trusted Locations\Location"
    Else
        strLnKey = ""
    End If

    Set oRegistry = Nothing

Open in new window


»bp

Author

Commented:
Appreciate the responses.   Bill that coding worked great.  You are awesome!
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Welcome, glad that helped.


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial