Error # 1004 was generated by Microsoft office Excel

[johnson34]

I tried to disect the visual basic code in the macro. I think it is trying to fail in this section (code given below). We are using informix database and informix ODBC connection to connect to database.

Note :- following O.S with office combination works okay with
a) Windows X.P 2002 O.S with Office Excel 2002 SP3.
b) Windows X.P 2002 SP2 O.S with Office [COLOR=blue! important][COLOR=blue! important]Excel [COLOR=blue! important]2003[/COLOR][/COLOR][/COLOR].

But it doesnt work with following O.S with office combination.
a) Windows X.P 2003 SP1 with office Excel 2002 SP2.


Following is the section of the code I think 80% it is erroring out :-
<!-- BEGIN TEMPLATE: bbcode_code -->Code:

With wksTemplate.QueryTables(1) .Connection = "ODBC;DSN=claim"' .Connection = cnctStr .CommandType = xlCmdSql .CommandText = cmd .Destination = Range("A5") .AdjustColumnWidth = False .BackgroundQuery = False .EnableEditing = False .EnableRefresh = True .FieldNames = False .FillAdjacentFormulas = True .PreserveColumnInfo = True .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .RowNumbers = False .SavePassword = True .SaveData = True .RefreshPeriod = 0 .Refresh BackgroundQuery:=False If .Refreshing Then MsgBox "Query is currently refreshing: please wait" Else .Refresh BackgroundQuery:=False End If​

</PRE>
<!-- END TEMPLATE: bbcode_code -->
The problem is this macro was created in old version of office and not running on latest version of office. When I try to click on to help when it pops out error. I see following list of things it is asking me to check in macro error

There is an error in the macro you were running. The specified method can't be used on the specified object for one of the following reasons:
a) An argument contains a value that is not valid
b) The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails
c) An external error occured, such as a failure to read or write from a file.
<!-- / message --><!-- edit note -->

 

[BrianB]

Don't know the answer - but in the absence of another reply ............
'----------------------------------------------------------------------
1. ERROR MESSAGE

I think it is trying to fail in this section

If you hit the debug button at the error it will take you to the problem line of code.
'----------------------------------------------------------------------
2. ".Connection" appears twice in the code. This could work OK because the second appearance would replace the first. In which case you need to check what the value of 'cnctstring' variable is.
'----------------------------------------------------------------------
3. I don't like the If ... End If code line. I suggest you omit this temporarily
'----------------------------------------------------------------------
4. A big problem of using different versions of Excel is that not all the properties of an object are the same in each. Once a QueryTable is set up it retains many of the properties. Perhaps a simple single code line would work better :-
wksTemplate.QueryTables(1).Refresh BackgroundQuery:=False
'----------------------------------------------------------------------
5. The structure of the code look wrong. It may be because of the later version of Excel.. but it could be the problem. The code below is more usual method :-

With wksTemplate.QueryTables(1)
    .Connection = "ODBC;DSN=claim"  ' ? duplicate
    .Connection = cnctStr           ' ? duplicate
    .CommandType = xlCmdSql
    .CommandText = cmd
    .Destination = Range("A5")
    .AdjustColumnWidth = False
    .BackgroundQuery = False
    .EnableEditing = False
    .EnableRefresh = True
    .FieldNames = False
    .FillAdjacentFormulas = True
    .PreserveColumnInfo = True
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .RowNumbers = False
    .SavePassword = True
    .SaveData = True
    .RefreshPeriod = 0
    .Refresh BackgroundQuery:=False
End With

'-----------------------------------------------------------------
6. The best solution would be to record the code whilst setting up the data link afresh via Data\Get external data. I cannot help with this having no experience of Informix
--------------------------------------------------------------------

Hope this helps.

 

[mae0429]

For #3 of Brian's points:

If you want to let the user know what's going on, why don't you change what the status bar says instead of popping up a MsgBox?

Throughout the code you can place:

Application.StatusBar = "Whatever your message is..."

And then at the end:

Application.StatusBar = False

Add as many as you like to notify the user of what is happening and also to identify if you get stuck in loop, etc.

-Matt

 

[johnson34]

Hi brian

Thanks for your suggestion,

You gave following suggestion :-
The best solution would be to record the code whilst setting up the data link afresh via Data\Get external data. I cannot help with this having no experience of Informix

Can you please ellobrate on how to do Data\Get external data? how do you establish it. Can you give me some examples.

regards,
john