XL97: Cannot Record External Data Range Properties

ID: Q165780

The information in this article applies to:

Microsoft Excel 97 for Windows

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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 kbweb xlvbahowto xlquery
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb