Workbooks.Add = Macro Regression in 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Abhijit - 13 Sep 2007 23:46 GMT

Hi,

It seems that the "Workbooks.Add" method has regressed from 2003 to 2007
when using "HTM" file. Here is the "one line macro" that demonstrates the
same. (The attached HTML open fine from "File/Open" menu or using
the API 'Workbook.Open')

'... Macro Start
Workbooks.Add "c:\temp\abc.htm"
'... Macro End

If I used the 'abc.htm' file (not sure how to attach to this post - hence
typing below) then XL 2007 throws up.

///////////// ABC.HTM//////////////////
<html><body><center>
    <h1>VectorPRO<sup>®</sup></h1>
    <h2>Support Resource Priority Report</h2>
    <table width="90%" border=1>
        <tr>
            <td style='color:white;border:.5pt solid
black;background:#4F81BD;'>Support Resource</td>
            <td style='color:white;border:.5pt solid black;background:#4F81BD;'> </td>
            <td style='color:white;border:.5pt solid
black;background:#4F81BD;'>Expected Start</td>
            <td style='color:white;border:.5pt solid
black;background:#4F81BD;'>Actual Start</td>
            <td width="20" style='font-size:7.0pt;color:white;border:.5pt solid
black;background:#4F81BD;'>Clr</td>
            <td style='color:white;border:.5pt solid black;background:#4F81BD;'>Task
Manager</td>
            <td style='color:white;border:.5pt solid black;background:#4F81BD;'>Task
Name </td>
        </tr>
        <tr>
            <td>Ujjwal Ghosh(Jr)</td>
            <td>Pressure testing of tuyers</td>
            <td>6/16/2007 09:00 AM</td>
            <td>6/16/2007 09:00 AM</td>
            <td width="20" style='font-size:7.0pt;background:#99CC00;'> </td>
            <td>N ADHIKARY</td>
            <td>OVERHAULING OF DRILL MACHINE</td>
        </tr>
    </table>
</center></body></html>

Reply to this Message

Bill Renaud - 14 Sep 2007 06:37 GMT

Should you be using the Open method, instead of Add? (This works for me
with Excel 2000.)

Workbooks.Open "c:\temp\abc.htm"

Signature

Regards,
Bill Renaud

Reply to this Message

Abhijit - 16 Sep 2007 22:04 GMT

Thanks Bill.

I did discover that by recording macro in 2007. But the question is more
deep rooted (sorry for being terse in my previous post).

I know there is no written commitment by MS to ensure that macros will
behave the same, but atleast they should not regress. Because now I am not
sure which macros will work and which will fail (for the features that have
not changed).

As I continue my journey I found that now the Sheet.Delete macro throws up
and message box (ok / cancel) which was not there earlier.

This is good news for development community because they will continue to
earn and survive with each new release of MS products. But as a consumer, it
puts question mark on ROI's of investments which many times go beyond few
years.

Any way, its different usless debate (as I doubt if MS reads this and doubt
more if they fix this).

Thanks for your answer.
----------------------------------------------------------

> Should you be using the Open method, instead of Add? (This works for me
> with Excel 2000.)
>
> Workbooks.Open "c:\temp\abc.htm"

Reply to this Message

Bill Renaud - 16 Sep 2007 22:49 GMT

The Workbooks.Add method was always intended to add a new, blank Excel
workbook to the workbooks collection. Any parameter that was included as
an argument was to specify a template (Chart, Excel 4 Macro sheet, or
worksheet).

<<As I continue my journey I found that now the Sheet.Delete macro
throws up a message box (ok / cancel) which was not there earlier.>>

I (still) use Excel 2000, and you always had to turn DisplayAlerts off
before deleting any sheets, otherwise the prompt would appear:

Application.DisplayAlerts = False
Worksheets("Data").Delete
Application.DisplayAlerts = True

So in Excel 2007, is there some new layer of security on top of this?

Signature

Regards,
Bill Renaud

Reply to this Message