asked on

Problem with Selection.QueryTable.Connection (Excel VBA)

Experts,

I am getting "RunTime Error 1004". Unable to open the link (http://test.....) which I am using in the code below. (I am new to this VBA and just read that this is a common error message and couldn't find the problem). Please help me out with this.

P.S : Following code was working with SQL 2k and recenlty updated to SQL 2005 and not working.

Here is the code
---------------------------------------
With Selection.QueryTable
.Connection = _
"URL;http://test/ODS?sql=select+*+from+myDatabase..MyTable+where+PNumber='" &
strPnumber & "'" & strPInd & "'+order+by+pNumber,status+desc+for+xml+auto&xsl=StyleSheet\selCLookup.xsl&root=root"
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Thanks

ScottMorris

Have you tried getting your final connection URL and checking the link? That is to say, add some debug code something like below then copy/paste the URL (found in your "Immediate Window" in the VBA interface) into a web browser to see if the link is valid.

With Selection.QueryTable
            strURL = "http://test/ODS?sql=select+*+from+myDatabase..MyTable+where+PNumber='" & 
      strPnumber & "'" & strPInd & "'+order+by+pNumber,status+desc+for+xml+auto&xsl=StyleSheet\selCLookup.xsl&root=root"
            Debug.Print strURL

            .Connection = "URL;" & strURL
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With

Open in new window

Tpaul_10

ASKER

1) When I open up my excel and click on the button, I am getting the following error.

Run-Time error ;1004':
Unable to open
URL;http://myurl
The Required internet protocol is not installed on your computer or the internet addess you requested may not be valid.

2)When I run the URL in a browser i am getting the following message

The specified module could not be found

Any help will be appreciated and is this any way related to SQLXML4 or put SQLXML3 ?

ScottMorris

It looks to me that the URL may be invalid. I don't know much at all about the server end of things, but I can tell you that it looks like that's where your problem is.

Tpaul_10

ASKER

Thanks for looking in to this ScottMorris.

When you say URL problem, is there any problem you can see in the code or is it problem with the server i am accessing?

Also, do I have this question in the right category? Please help me to move this to correct one.

THANKS AGAIN for looking in to this.

ASKER CERTIFIED SOLUTION

ScottMorris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tpaul_10

ASKER

Basically the question is How to connect to the SQL2005 using EXCEL VBA code?
Any help is appreciated