I'm trying to create a TDA Automation with an export to an existing Excel file (xlsx and xlsm); however, when ever I select the specific Excel file to export to I get the error
'Specified argument was out of the range of valid values'
See screen shot SOL73459_Fig_1.jpg
TDA cannot handle custom style in Excel files of xlsx or xlsm. See screen shot SOL73459_Fig_2.JPG. See Book1_0.xlsx for an example of an Excel file with custom style.
WORKAROUND: You can delete the custom styles in your Excel file one at a time (no multi-select/delete possible) or use the following Marco in your Excel file to delete all the custom styles at once.
1. Turn on the Developer tab in your Excel file with the following steps
a. Click the 'Microsoft Office Button' and then click 'Excel Options' (SOL73459_Fig_3.JPG)
b. Click 'Popular', and then select the 'Show Developer tab in the Ribbon check box'
2. Click on the Developer tab and select the Macros icon
3. In the Marcos window, type in 'StyleKill' as a 'Macro Name' and click on 'Create'
4. Copy the following code into the Visual Basic window and click on 'Save' (SOL73459_Fig_4.JPG)
Sub StyleKill()
Dim styT As Style
Dim intRet As Integer
On Error Resume Next
For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then
If styT.Name <> "1" Then styT.Delete
End If
Next styT
End Sub
5. Click on the Macro icon again
6. Select the StyleKill Macro that you just created and click 'Run'
STATUS: Waiting for fix in a future release of Toad for Data Analyst
