Microsoft KB Archive/188236

= XL98: Cannot Record Macro to Change External Data Properties =

Article ID: 188236

Article Last Modified on 6/17/2005

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q188236



SYMPTOMS
In Microsoft Excel 98 Macintosh Edition, 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 when you run it. 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 records macro code incorrectly when you open the External Data Range Properties dialog box. The recorded code looks similar to the following: ExecuteExcel4Macro "(,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,1,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 Microsoft Excel 98 Macintosh Edition.



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.

Additional query words: XL98

Keywords: kbbug kbpending KB188236

-

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

© Microsoft Corporation. All rights reserved.