XL97: Cannot Record External Data Range Properties

• 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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

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

The following example 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

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