Microsoft KB Archive/165780

= XL97: Cannot Record External Data Range Properties =

Article ID: 165780

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q165780



SYMPTOMS
In Microsoft Excel 97, if you open the External Data Range Properties dialog box while you record a Visual Basic for Applications macro, the resulting recorded code does not work. If you run the recorded macro, you receive the following error message:

Run-time error '1004':

The formula you typed contains an error.



CAUSE
This problem occurs because the macro recorder in Microsoft Excel 97 records macro code incorrectly when you open the External Data Range Properties dialog box. The recorded code looks similar to the following: ExecuteExcel4Macro "(,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,0,TRUE,TRUE,TRUE)"



WORKAROUND
To work around this problem, remove any lines of code that resemble the incorrect code from your macro. After you do this, use the information in the "More Information" section in this article to edit your macro to specify the appropriate settings for the External Data Range Properties dialog box.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Each control in the External Data Range Properties dialog box has a corresponding Visual Basic for Applications property or method. The following table shows each control and the matching Visual Basic property or method.   Control                      Property/Method --  Name                         Name property

Save query definition       Delete method

Save password               SavePassword property

Enable background refresh   BackgroundQuery property

Refresh data on file open   RefreshOnFileOpen property

Remove external data from   SaveData property worksheet before saving

Include field names         FieldNames property

Include row numbers         RowNumbers property

Autoformat data             HasAutoFormat property

Import HTML table(s) only   TablesOnlyFromHTML property

Insert cells for new data,  RefreshStyle property delete unused cells         (xlInsertDeleteCells)

Insert entire rows for new  RefreshStyle property data, clear unused cells    (xlInsertEntireRows)

Overwrite existing cells    RefreshStyle property with new data, clear        (xlOverwriteCells) unused cells

Fill down formulas in       FillAdjacentFormulas property columns adjacent to data Note that it is not necessary to specify all the properties; you need to set only the properties that you want to change.

The following sample macro demonstrates how you can use each of these properties and methods when you format external data ranges: Sub TestDataRangeProperties

With Sheets("Sheet1").QueryTables(1)

' Change the name of the external data range. .Name = "ExternalData1" ' Save the password with the external data range so that you do          ' not have to enter it again. .SavePassword = True

' Allow querying to occur in the background while you perform ' other tasks. .BackgroundQuery = True

' Automatically update the data range when the workbook is          ' opened. .RefreshOnFileOpen = True

' Save the actual data with the workbook. If you set this ' property to False, only the query definition is saved with ' the workbook, so that the data can be retrieved when ' necessary. This property can only be set to False if the ' RefreshOnFileOpen property is set to True. .SaveData = True

' No field names at the top of the data range. .FieldNames = False

' No row numbers down the left side of the data range. .RowNumbers = False

' Apply autoformatting to the data range whenever it is          ' updated. .HasAutoFormat = True

' If the external data source is a Web page, read only the ' tables from the Web page. Otherwise, this has no effect. .TablesOnlyFromHTML = True

' Set the refresh style for the data range. .RefreshStyle = xlOverwriteCells

' Don't automatically fill formulas that are adjacent to the ' data range. .FillAdjacentFormulas = False

End With

End Sub Note that the Delete method permanently removes the query definition from the data range. For example: Sheets("Sheet1").QueryTables(1).Delete If you run a macro that contains this code, the query definition is removed and its properties are no longer accessible. However, the data and formatting remain in the worksheet.

Additional query words: XL97

Keywords: kberrmsg kbmacro kbprb kbweb KB165780

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.