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
Tpaul_10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ScottMorrisCommented:
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

0
Tpaul_10Author Commented:
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 ?
0
ScottMorrisCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tpaul_10Author Commented:
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.
0
ScottMorrisCommented:
"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?"

I can't directly rule either out.  What you need to do is to find a properly working URL for your active server, whether that's by using different tools that interface with the server, other documentation, etc and see what syntax it uses.  Then see if it matches the syntax that the code puts together.  If everything looks good, make sure that the URL gets formed correctly (again, doing the debug code that I mention above, look VERY closely at your results).

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

Visual Basic v1.0.5.x is probably not the right category for this question.  I would put it in either "MS Excel" or "MS SQL Server 2005" just in case there's some other known issue that I'm not aware of (like I said, I'm not familiar with the differences between SQL Server 2k and SQL Server 2k5.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tpaul_10Author Commented:
Basically the question is How to connect to the SQL2005 using EXCEL VBA code?
Any help is appreciated
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.