Link to home
Start Free TrialLog in
Avatar of Vorenus
Vorenus

asked on

Excel 2003 / VB6 : Problem on Query Table Refresh

Hello,

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.
Avatar of yogi4life
yogi4life
Flag of Norway image

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...
Avatar of Vorenus
Vorenus

ASKER

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
      .Refresh
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...

Thanks.
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....
Avatar of Vorenus

ASKER

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

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

Avatar of Vorenus

ASKER

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...
ASKER CERTIFIED SOLUTION
Avatar of yogi4life
yogi4life
Flag of Norway image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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?
Avatar of Vorenus

ASKER

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.