excelvbscripthta

ACE OLEDB provider in vbscript in HTML application (hta) to read an excel says provider not installed, vbs is working but hta is not working

I want to read an excel sheet and display the contents with vbscript. I also want the user to be able to pick the source directory, hence the addition of hta furteron. The ACE OLEDB providor has to be used (please don't ask, I know this is end of life and not sustainable etc, but I cannot change this right now), so I installed the Microsoft Access Database Engine 2016 Redistributable (accessdatabaseengine_X64.exe) from here: https://www.microsoft.com/en-us/download/details.aspx?id=54920. Furtermore I have Microsoft 365 Apps for Enterprise installed (x64) on my laptop with Windows 11. I created an excel file with 1 sheet, 2 columns and a few rows of content like this:

enter image description here

and named the hihglighted blok as 'namenblok' and saved the file as test.xlsx. Then created a file called test.vbs in the same directory with this content:

Option Explicit

Dim fso         : set fso = CreateObject("Scripting.FileSystemObject")
Dim g_sCfg      : g_sCfg        = fso.GetFolder(fso.GetAbsolutePathName(".")) & "\" & "test.xlsx"
Dim connectString : connectString  =  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & g_sCfg & ";Extended Properties=""Excel 12.0;HDR=Yes;"";"

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Dim objConnection, objRecordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset  = CreateObject("ADODB.Recordset")
objConnection.Open connectString
' wscript.sleep 1000
             
objRecordset.CursorLocation = adUseClient
On Error Resume Next

objRecordset.Open "SELECT * FROM namenblok" , objConnection, adOpenStatic, adLockOptimistic ' namenblok is een op naam gedefinieerde blok aan cellen

If Err.Number <> 0 Then
    'error handling:
    WScript.Echo Err.Number & " Srce: " & Err.Source & " Desc: " &  Err.Description
    Err.Clear
End If

Do While Not(objRecordset.EOF)
 wscript.echo objRecordset("id") & " " & objRecordset("naam")
 objRecordset.MoveNext
Loop

objRecordset.Close
objConnection.Close

When I run this (eg, double click it), it nicely displays all rows from the excel sheet one by one like this:

enter image description here

But when changed to a .hta (html application) and adding only the script parts like this:

<script language="VBScript">
Option Explicit
…
…
objConnection.Close
</script>

it says when ran: "Provider cannot be not found. It may not be properly installed." and points to line 15 with: objConnection.Open connectString:

enter image description here

I probably have to adjust the html part because of changed html restrictions, but cannot find how and what exactly. Any help would be appreciated!

Solution

  • The error is occurring because the HTA is running in 32 bit mode, whereas the standalone VBS script is running in 64 bit mode.

    HTA scripts run in 32 bit mode by default. VBS scripts run in 64 bit mode by default.

    If you open a Cmd prompt and run the HTA in 64 bit mode by running mshta.exe from System32 and providing the full path to your HTA on the command line, your HTA will run correctly.

    For example:

    cd /d d:\temp\wpb2
mshta d:\temp\wpb2\test4.hta

    Note: By default, C:\Windows\System32 is on the search path, so entering mshta will normally work fine, but to ensure that you're running the 64 bit version, regardless of path settings, specify the full path of C:\Windows\System32\mshta.exe.

    Note: The VBSCript code posted in the question uses WScript.Echo. That must be changed to MsgBox in order to work from an HTA because the host is mshta.exe instead of wscript.exe or cscript.exe.

    Note: The VBSCript code posted in the question requires that the current folder be the script's folder (assuming that the XLSX file is in the same folder as the script). Otherwise, the script will stop with the error: The Microsoft Access database engine cannot open or write to the file. To ensure that the current folder is the script's folder, the following code can be added to the top of the VBS script within the HTA file:

    Dim oFSO, oWSH, MyPath, MyFolder
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oWSH = CreateObject("Wscript.Shell")
MyPath = Mid(document.URL,8)
MyFolder = oFSO.GetParentFolderName(MyPath)
oWSH.CurrentDirectory = MyFolder

    If the XLSX file is in a different folder than the script, then you would just explicitly set that folder as the current folder like this:

    Dim oFSO, oWSH
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oWSH = CreateObject("Wscript.Shell")
oWSH.CurrentDirectory = "d:\temp\wpb2"