Excel 2003 / VB6 : Problem on Query Table Refresh


I'm maintaining a VB6 reporting class using the Excel automation.
I'm living a nightmare about a random runtime error 1004 : "Application defined or object defined" which happens when I do a .Refresh BackGroundQuery:=False.

I am programmatically creating Excel QueryTables to retrieve data from a SQL Server database to an Excel spreadsheet that I then save. The Excel spreadsheet is entirely made programmatically and is not protected in any kind of way etc...

I've seen lots of people with similar errors when using query tables... but few reported working solutions... and even then I tried what they did but it doesn't work for me...

The code is quite large and has a lot of dependencies because it is part of a bigger project which uses a lots of in-house components so it isn't easy to post a small working code reproducing the problem here, but I believe the problem doesn't lie in my code per se but rather in Excel considering that the error is very random even on the same dataset...
That said, I believe that a workaround may exist to force Excel to behave correctly somehow (setting a property, something like that);

Did anyone encounter similar issues and what did you do to solve them?

Thanks a lot in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

What are the parameters you set up for your QueryTables?

How is the network infrastructure in respect to your SQL Server database...
Is there any time-out situations that might occur due to long running queries?

In your situation I might try to make some small code to replace the QueryTables... It is not many lines of VBA code or VB code you would need to do a query to a table... Then you could investigate and debug the situation on a deeper level...
VorenusAuthor Commented:
Hi yogi,

Here is more or less what I'm doing...

With destSheet.QueryTables.Add(Connection:=strConnection, Destination:=Cells(destRow, destColumn), SQL:=sqlQuery)
      .EnableRefresh = False
        .EnableEditing = False
        .RefreshOnFileOpen = False
        .FieldNames = True
       .RowNumbers = True
        .BackgroundQuery = False
End With

Nothing particularily unusual I think

The SQL Server database is localhost for testing, which should rule out a network or timeout issue...
I have considered replacing the query tables but I also need the column names... unless you have an easy way to retrieve them along the results sets?
Also a lot of things are built on top of query tables and I'm afraid it could be much more work than it initially seems... I don't want to break more things than I fix in the process...

yes there is a way of retrieving the columns names... both directly in SQL or alternatively you can retrieve the column names in ADO without knowing the MS SQL way of retrieving the meta-data for the table....
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

VorenusAuthor Commented:
Is it possible to do it in one shot (column headers + data) ? (like the querytable does)

Yes... It is quite possible to do one select which joins the appropriate information if you need to do it all in pure SQL or via a stored procedure. If you resort to ADO it is a bit simpler because you don't neccessarly need to do a join but can loop around it.

If you want it as one resultset you might convert all columns to text ... which might not be good for your code or solution... or you might
need an added column which i text/string which has a comma/semicolon separated list of column names... that way you can keep the datatypes...

VorenusAuthor Commented:
Actually, I was meaning a feature from ADO for example...
There are hundreds of stored procedure using this component and I can't possibly modify the SQL code for all of them, which is why I was reluctant moving away from QueryTables for the time being...
Sub ReportSomeColumnInfoWithAQuery()
    Dim conn As ADODB.Connection
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider='sqloledb';Data Source='SQLserver01\SQLEXPRESS';" & "Initial Catalog='master';Integrated Security='SSPI';"
    Set rs = CreateObject("ADODB.recordset")
    rs.Open "Select * from myDatabase..myTable", conn
    For Each x In rs.fields
      Debug.Print x.Name & " " & x.Value
End Sub

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
So - are you saying that this VB6 component is using Excel with QueryTables and that the component is being used by SQL Server stored procdures?
VorenusAuthor Commented:
Thanks a lot, and sorry for the delay in awarding the points.

I think the problem was due to not having qualitied the QueryTable.Add call with the sheet and it sometimes caused problems.
However, I replaced them with CopyFromRecordset as it seems a bit more reliable...

Thanks again.
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.