The following Excel Macro will convert the IBD XLS file into a MS SYM file automatically and save it in the Downloader directory. It creates a unique file for each date, but will overwrite an existing SYM file if the macro is run on the same set of data more than once. It does not adjust your original XLS file in any way. Feedback is welcome!

Note: You will need to modify the 'targetFile' path to reflect your Downloader path.
Note: You will need to modify the 'Selection.FormulaR1C1' line to remove the "us;" prefix if you do not receive QC data.

[code:1:176658599e]
Sub IBDConversion()
' Convert Investors Business Daily XLS to MetaSock SYM
' Written by G. Stockman, 2004-2006. All rights reserved.
' Updated: June 4, 2006 @ 2050 CST

' Supress screen updates
Application.ScreenUpdating = False
' Remember date of market close
Dim dString As String
dString = Range("A2").Value
Dim strSplit() As String
strSplit = Split(dString, " ")
dString = strSplit(3)
' Format date (remove string separators)
Dim pos As Integer
pos = InStr(dString, "/")
dString = Left(dString, pos - 1) & Mid(dString, pos + 1)
pos = InStr(dString, "/")
dString = Left(dString, pos - 1) & Mid(dString, pos + 1)
' Copy raw source data to a new workbook
Range("B7:C106").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
' Create SYM compatible template
Range("A1:A100").Select
Selection.EntireColumn.Insert
Selection.FormulaR1C1 = "=RC[1] & "","" & ""us;"" & RC[2] & "","" & ""N/A"" & "","" & ""0"" & "","" & ""0"""
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Delete raw source code
Columns("B:C").Select
Selection.Delete
' Save new SYM file
Dim targetFile As String
targetFile = "C:\\Program Files\\Equis\ he DownLoader\\ibd100_" & dString & ".sym"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=targetFile, FileFormat:=xlTextPrinter, CreateBackup:=False
Application.DisplayAlerts = True
' Close temporary workbook
ActiveWorkbook.Close False
Range("A1").Select
' Resume screen updates
Application.ScreenUpdating = True
' Completion alert
MsgBox ("Conversion completed!")
End Sub
[/code:1:176658599e]
Convert the XLS data:

To SYM data:

Just tried to run the IBDConversion script --

Error Msg from Microsoft Visual Basic:

Run-time error '1004';
Method 'Add' of object 'Workbooks' failed.


THEN-I figured out that I should have SAVED the IBD100 spreadsheet to an *.xls file on my PC,
and not try to convert the spreadsheet that was displayed in the HTML window to the *.sym file.

Hope that helps if anyone runs into the same issue.

Thanks, Wabbit. I'll add this step to my next revision. I think Eric's feedback is great, cause it allows me to make the code all the more bulletproof. You know I appreciate your help (as always). :)

The original code is not ready to anticipate all possible errors since I developed it to meet my own requirements. In order for this function to work properly, a worksheet must be active in Excel. Wabbit's code is the foundation for doing something aside from reporting the error. e.g., if the worksheet is not active in Excel, start Excel and copy the online data. I will develop this soon.

[code:1:4dae22e9d2]Sub IBDConversion()
' Convert local Investors Business Daily XLS to MetaSock SYM
' Written by G. Stockman, 2004. All rights reserved.
' Updated: July 29, 2006 @ 1430 CST

' Error handler
On Error GoTo ErrorHandler
' Supress screen updates
Application.ScreenUpdating = False
' Remember date of market close
Dim dString As String
dString = Range("A2").Value
Dim strSplit() As String
strSplit = Split(dString, " ")
dString = strSplit(3)
' Format date (remove string separators)
Dim pos As Integer
pos = InStr(dString, "/")
dString = Left(dString, pos - 1) & Mid(dString, pos + 1)
pos = InStr(dString, "/")
dString = Left(dString, pos - 1) & Mid(dString, pos + 1)
' Copy raw source data to a new workbook
Range("B7:C106").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
' Create SYM compatible template
Range("A1:A100").Select
Selection.EntireColumn.Insert
Selection.FormulaR1C1 = "=RC[1] & "","" & ""us;"" & RC[2] & "","" & ""N/A"" & "","" & ""0"" & "","" & ""0"""
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
' Delete raw source code
Columns("B:C").Select
Selection.Delete
' Save new SYM file
Dim targetFile As String
targetFile = "C:\\Program Files\\Equis\ he DownLoader\\ibd100_" & dString & ".sym"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=targetFile, FileFormat:=xlTextPrinter, CreateBackup:=False
Application.DisplayAlerts = True
' Close temporary workbook
ActiveWorkbook.Close False
Range("A1").Select
' Resume screen updates
Application.ScreenUpdating = True
' Completion alert
MsgBox ("Conversion completed!")
' Exit macro normally
Exit Sub
' Handle abnormal situations
ErrorHandler:
Dim Response As Integer
Response = -1
Dim ErrorMsg01 As String
ErrorMsg01 = "Local IBD XLS file cannot be found!"
Dim ErrorMsg99 As String
ErrorMsg99 = "The following unexpected error has occurred: "
Select Case Err.Number
Case 1004
If Err.Description = "Method 'Add' of object 'Workbooks' failed" Then
Response = MsgBox(ErrorMsg01 & vbNewLine, _
vbOKOnly + vbCritical, "Error: 1004")
Exit Sub
Else
Response = MsgBox(ErrorMsg99 & vbNewLine & vbNewLine & Err.Description, _
vbOKOnly + vbCritical, "Error: 1004")
Exit Sub
End If
Case Else
Response = MsgBox(ErrorMsg99 & vbNewLine & vbNewLine & Err.Description, _
vbOKOnly + vbCritical, "Unexpected Error")
Exit Sub
End Select
End Sub[/code:1:4dae22e9d2]

Wow. I think it's a compliment that one's work gets public attention. I sorta feel like I'm on stage in my underwear. [:$]