Error # 1004 was generated by Microsoft office Excel

johnson34

New Member
Joined
Jul 21, 2008
Messages
2
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 -->
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-

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

Board Regular
Joined
Jun 12, 2008
Messages
114
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:
Code:
Application.StatusBar = "Whatever your message is..."

And then at the end:
Code:
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

New Member
Joined
Jul 21, 2008
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,459
Messages
5,596,266
Members
414,049
Latest member
MisterExcel26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top