Excel SmartView vb function HypConnect not working / error code 100000

Question

In the code below, I am trying to automate SmartView retrievals in a number of worksheets. The code seems to work OK up to the point that I create a connection before I start the loop. Inside the loop, I want to use HypConnect to use the connection - but it returns an rerror code of 100000 and doesn't connect. When I test some of the status variables, I see that the conection exists but that the sheet isn't connected.

I've copied in the full smartview.bas file into another module in the same workbook.

At the point when I call HypConnect, all four arguments are defined and have the proper values.

I don't know what else may be wrong.

Sub Retrieval()

URL = Sheets("Index").Range("E5").Value
Server = Sheets("Index").Range("E6").Value
App = Sheets("Index").Range("E7").Value
Db = Sheets("Index").Range("E8").Value
UserId = Sheets("Index").Range("E10").Value
psw = InputBox("Password")
DSN = "Source"

x = HypDisconnectAll()
If HypConnectionExists(DSN) Then x = HypRemoveConnection(DSN)
x = HypCreateConnection( _
    Empty, _
    UserId, _
    psw, _
    "Essbase", _
    URL, _
    Server, _
    App, _
    Db, _
    DSN, _
    "Connection for retrievals")
x = HypSetConnAliasTable("Source", "Code_and_Desc")
x = HypSetGlobalOption(5, 3)     'turn off warnings
x = HypSetGlobalOption(6, True)  'use thousands separator
x = HypSetGlobalOption(15, True) 'retain formats

For Each Sheet In Worksheets
    Sheet.Activate
    RR = Range("A1").Value
    If RR = "" Then RR = "A1"
    If RR <> "Do not retrieve" Then
        x = HypConnect(Sheet.Name, UserId, psw, DSN)

        x = HypSetSheetOption(Sheet.Name, 5, 0)
        x = HypSetSheetOption(Sheet.Name, 6, False)
        x = HypSetSheetOption(Sheet.Name, 7, False)
        x = HypSetSheetOption(Sheet.Name, 8, False)
        x = HypSetSheetOption(Sheet.Name, 13, "#numericzero")
        x = HypSetSheetOption(Sheet.Name, 16, 2)

        x = HypRetrieveRange(Sheet.Name, RR, DSN)
        End If

    Next Sheet

x = HypSetGlobalOption(5, 2) 'turn warnings back on
x = HypRemoveConnection(DSN)
x = HypDisconnectAll()

MsgBox ("Done")
End Sub

score 0 · Answer 1 · answered Mar 18 '15 at 14:22

As many times as I checked it yesterday, I had the server name incorrect and couldn't find it. Today, with fresher eyes, I found the problem and the above code works fine. Although the problem was actually in the HypCreateConnection line, that function call returned a 0 error code. Then using HypConnect with an invalid connection caused the error - and returned an undocumented error code.

Excel SmartView vb function HypConnect not working / error code 100000

1 Answers1