XL98: Cannot Record Macro to Change External Data Properties

 ExecuteExcel4Macro "(,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,1,TRUE,TRUE,TRUE)"

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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 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
				

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.