COM. Can't open an Excel workbook

09-11-2006

I am trying to open an Excel workbook with PowerShell (never tried yet
before). The code

Code:

$xl = New-Object -COM Excel.Application
$bk = $xl.Workbooks.Open('C:\TEMP\Book1.xls')

gives me the following error:

Exception calling "Open" with "1" argument(s): "Old format or invalid type
library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At C:\rom\aps\2do\Test-Excel.ps1:2 char:25
+ $bk = $xl.Workbooks.Open( <<<< 'C:\TEMP\Book1.xls')

Has anybody ever seen this error? What can be wrong here? How do I fix the
problem?

NB:
*) the similar Perl script works fine with the same .xls file.
*) I tried few more .xls and .csv files - the same error.
*) I tried the full set of parameters for Open() - it does not help

--
Thanks,
Roman

09-11-2006

Sorry, I don't know that error message.
I wonder, does this example script work, or does it also give you an error:
http://www.microsoft.com/technet/scr...6/hey0908.mspx

--
greetings
dreeschkind

"Roman Kuzmin" wrote:

> I am trying to open an Excel workbook with PowerShell (never tried yet
> before). The code
>
>

Code:

> $xl = New-Object -COM Excel.Application
> $bk = $xl.Workbooks.Open('C:\TEMP\Book1.xls')
>

>
> gives me the following error:
>
> Exception calling "Open" with "1" argument(s): "Old format or invalid type
> library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
> At C:\rom\aps\2do\Test-Excel.ps1:2 char:25
> + $bk = $xl.Workbooks.Open( <<<< 'C:\TEMP\Book1.xls')
>
> Has anybody ever seen this error? What can be wrong here? How do I fix the
> problem?
>
> NB:
> *) the similar Perl script works fine with the same .xls file.
> *) I tried few more .xls and .csv files - the same error.
> *) I tried the full set of parameters for Open() - it does not help
>
> --
> Thanks,
> Roman

09-11-2006

It does not work for me; the error is the same:

Exception calling "Add" with "0" argument(s): "Old format or invalid type
library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At C:\rom\aps\2do\excel.ps1:5 char:22
+ $b = $a.Workbooks.Add( <<<< )

--
Thanks,
Roman

09-11-2006

The problem looks well known, the answer is probably here:
http://support.microsoft.com/default...b;en-us;320369

I will try the suggested workaround.

(Nevertheless, as I said, Perl script works fine...)

--
Thanks,
Roman

09-11-2006

> (Nevertheless, as I said, Perl script works fine...)

As well all know perl is a great language, and being so, when it has
errors, one of its error handling techinques is to automatically search
MSDN for answers / workarounds and adapt the code on the fly to
accomodate that

Karl Prosser (just woke up)

09-12-2006

I tried two workarounds suggested at
http://support.microsoft.com/default...b;en-us;320369

WORKAROUND 1

Code:

 (VBasic)
Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True
Dim oBooks As Object = oApp.Workbooks
Dim ci As System.Globalization.CultureInfo = New
System.Globalization.CultureInfo("en-US")
oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod,
Nothing, oBooks, Nothing, ci)

It requires getting a type of Workbooks property. This way in PowerShell
gives me an error:

PS> $xl = New-Object -COM Excel.Application
PS> $xl.Workbooks.GetType()
Method invocation failed because [System.__ComObject] doesn't contain a
method named 'GetType'.
At line:1 char:22
+ $xl.Workbooks.GetType( <<<< )

Any ideas how to solve this problem and still use this workaround?

WORKAROUND 2

Code:

 (VBasic)
Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True
Dim oldCI As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
oApp.Workbooks.Add()
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

It turned out that I just can't change the current culture in this way. See
the thread "How do I explicitly set the CurrentCulture?"

CONCLUSION

Both known workarounds do not work in PowerShell. Thus, automation of MS
Excel in PowerShell looks problematic for users with en-US Excel but non
en-US current locale. Any ideas are welcome.

--
Thanks,
Roman

09-12-2006

"Roman Kuzmin" wrote:

> I tried two workarounds suggested at
> http://support.microsoft.com/default...b;en-us;320369
>
> WORKAROUND 1
>
>

Code:

 (VBasic)
> Dim oApp As New Excel.Application()
> oApp.Visible = True
> oApp.UserControl = True
> Dim oBooks As Object = oApp.Workbooks
> Dim ci As System.Globalization.CultureInfo = New
> System.Globalization.CultureInfo("en-US")
> oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod,
> Nothing, oBooks, Nothing, ci)
>

>
> It requires getting a type of Workbooks property. This way in PowerShell
> gives me an error:
>
> PS> $xl = New-Object -COM Excel.Application
> PS> $xl.Workbooks.GetType()
> Method invocation failed because [System.__ComObject] doesn't contain a
> method named 'GetType'.
> At line:1 char:22
> + $xl.Workbooks.GetType( <<<< )
>
> Any ideas how to solve this problem and still use this workaround?
>
> WORKAROUND 2
>
>

Code:

 (VBasic)
> Dim oApp As New Excel.Application()
> oApp.Visible = True
> oApp.UserControl = True
> Dim oldCI As System.Globalization.CultureInfo = _
>     System.Threading.Thread.CurrentThread.CurrentCulture
> System.Threading.Thread.CurrentThread.CurrentCulture = _
>     New System.Globalization.CultureInfo("en-US")
> oApp.Workbooks.Add()
> System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
>

>
> It turned out that I just can't change the current culture in this way. See
> the thread "How do I explicitly set the CurrentCulture?"
>
> CONCLUSION
>
> Both known workarounds do not work in PowerShell. Thus, automation of MS
> Excel in PowerShell looks problematic for users with en-US Excel but non
> en-US current locale. Any ideas are welcome.
>
> --
> Thanks,
> Roman
>
>
>

09-12-2006

Resend , as code was lost :

You need to use PSBase :

MowPS>$excel.workbooks.add()
Exception calling "Add" with "0" argument(s): "Old format or invalid type
library. (Exception from HRESULT: 0x80028018
(TYPE_E_INVDATAREAD))"
At line:1 char:21

# Working Code :

$excel = New-object -com Excel.Application
$ci = [System.Globalization.CultureInfo]'en-us'
$excel.workbooks.psbase.gettype().InvokeMember("Add",
[Reflection.BindingFlags]::InvokeMethod,
$null, $excel.workbooks, $null, $ci)

MowPS>$excel.workbooks.item('book1')

Application : System.__ComObject
Creator : 1480803660
Parent : System.__ComObject

"/\/\o\/\/ [MVP]" wrote:

>
>
> "Roman Kuzmin" wrote:
>
> > I tried two workarounds suggested at
> > http://support.microsoft.com/default...b;en-us;320369
> >
> > WORKAROUND 1
> >
> >

Code:

 (VBasic)
> > Dim oApp As New Excel.Application()
> > oApp.Visible = True
> > oApp.UserControl = True
> > Dim oBooks As Object = oApp.Workbooks
> > Dim ci As System.Globalization.CultureInfo = New
> > System.Globalization.CultureInfo("en-US")
> > oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod,
> > Nothing, oBooks, Nothing, ci)
> >

> >
> > It requires getting a type of Workbooks property. This way in PowerShell
> > gives me an error:
> >
> > PS> $xl = New-Object -COM Excel.Application
> > PS> $xl.Workbooks.GetType()
> > Method invocation failed because [System.__ComObject] doesn't contain a
> > method named 'GetType'.
> > At line:1 char:22
> > + $xl.Workbooks.GetType( <<<< )
> >
> > Any ideas how to solve this problem and still use this workaround?
> >
> > WORKAROUND 2
> >
> >

Code:

 (VBasic)
> > Dim oApp As New Excel.Application()
> > oApp.Visible = True
> > oApp.UserControl = True
> > Dim oldCI As System.Globalization.CultureInfo = _
> >     System.Threading.Thread.CurrentThread.CurrentCulture
> > System.Threading.Thread.CurrentThread.CurrentCulture = _
> >     New System.Globalization.CultureInfo("en-US")
> > oApp.Workbooks.Add()
> > System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
> >

> >
> > It turned out that I just can't change the current culture in this way. See
> > the thread "How do I explicitly set the CurrentCulture?"
> >
> > CONCLUSION
> >
> > Both known workarounds do not work in PowerShell. Thus, automation of MS
> > Excel in PowerShell looks problematic for users with en-US Excel but non
> > en-US current locale. Any ideas are welcome.
> >
> > --
> > Thanks,
> > Roman
> >
> >
> >

09-12-2006

/\/\o\/\/, thanks!

The task was to open in Excel a file $src and save it as $res using another
Excel format $fmt. The working code now is:

Code:

$excel = New-Object -COM Excel.Application
$ci = [System.Globalization.CultureInfo]'en-US'
$book = $excel.Workbooks.PSBase.GetType().InvokeMember(
'Open', [Reflection.BindingFlags]::InvokeMethod, $null,
$excel.Workbooks, $src, $ci)
[void]$book.PSBase.GetType().InvokeMember(
'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $book, ($res,
$fmt), $ci)
[void]$book.PSBase.GetType().InvokeMember(
'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $book, 0, $ci)

It looks pretty ugly, but it is the only working way I know now.

--
Thanks,
Roman

02-20-2007

Hi,

there's another workaround, which I think highlights the 'early' status of
PowerShell.

If you change your regional settings in Control Panel to English-U.S,
scripts seem to work as Microsoft Script Center suggests they should. I'm
sure the next service release of PowerShell will cater for the fact that not
every user is US-based!

Cheers,

Mike

"Roman Kuzmin" wrote:

> /\/\o\/\/, thanks!
>
> The task was to open in Excel a file $src and save it as $res using another
> Excel format $fmt. The working code now is:
>
>

Code:

>
> $excel = New-Object -COM Excel.Application
> $ci = [System.Globalization.CultureInfo]'en-US'
> $book = $excel.Workbooks.PSBase.GetType().InvokeMember(
>     'Open', [Reflection.BindingFlags]::InvokeMethod, $null,
> $excel.Workbooks, $src, $ci)
> [void]$book.PSBase.GetType().InvokeMember(
>     'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $book, ($res,
> $fmt), $ci)
> [void]$book.PSBase.GetType().InvokeMember(
>     'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $book, 0, $ci)
>
>

>
> It looks pretty ugly, but it is the only working way I know now.
>
> --
> Thanks,
> Roman
>

09-11-2006	#1 (permalink)
=?Utf-8?B?Um9tYW4gS3V6bWlu?= Guest n/a posts	COM. Can't open an Excel workbook I am trying to open an Excel workbook with PowerShell (never tried yet before). The code Code: $xl = New-Object -COM Excel.Application $bk = $xl.Workbooks.Open('C:\TEMP\Book1.xls') gives me the following error: Exception calling "Open" with "1" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" At C:\rom\aps\2do\Test-Excel.ps1:2 char:25 + $bk = $xl.Workbooks.Open( <<<< 'C:\TEMP\Book1.xls') Has anybody ever seen this error? What can be wrong here? How do I fix the problem? NB: ) the similar Perl script works fine with the same .xls file. ) I tried few more .xls and .csv files - the same error. *) I tried the full set of parameters for Open() - it does not help -- Thanks, Roman
My System Specs

09-11-2006	#2 (permalink)
=?Utf-8?B?ZHJlZXNjaGtpbmQ=?= Guest n/a posts	RE: COM. Can't open an Excel workbook Sorry, I don't know that error message. I wonder, does this example script work, or does it also give you an error: http://www.microsoft.com/technet/scr...6/hey0908.mspx -- greetings dreeschkind "Roman Kuzmin" wrote: > I am trying to open an Excel workbook with PowerShell (never tried yet > before). The code > > Code: > $xl = New-Object -COM Excel.Application > $bk = $xl.Workbooks.Open('C:\TEMP\Book1.xls') > > > gives me the following error: > > Exception calling "Open" with "1" argument(s): "Old format or invalid type > library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" > At C:\rom\aps\2do\Test-Excel.ps1:2 char:25 > + $bk = $xl.Workbooks.Open( <<<< 'C:\TEMP\Book1.xls') > > Has anybody ever seen this error? What can be wrong here? How do I fix the > problem? > > NB: > ) the similar Perl script works fine with the same .xls file. > ) I tried few more .xls and .csv files - the same error. > *) I tried the full set of parameters for Open() - it does not help > > -- > Thanks, > Roman
My System Specs

09-11-2006	#3 (permalink)
=?Utf-8?B?Um9tYW4gS3V6bWlu?= Guest n/a posts	RE: COM. Can't open an Excel workbook It does not work for me; the error is the same: Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" At C:\rom\aps\2do\excel.ps1:5 char:22 + $b = $a.Workbooks.Add( <<<< ) -- Thanks, Roman
My System Specs

09-11-2006	#4 (permalink)
=?Utf-8?B?Um9tYW4gS3V6bWlu?= Guest n/a posts	RE: COM. Can't open an Excel workbook The problem looks well known, the answer is probably here: http://support.microsoft.com/default...b;en-us;320369 I will try the suggested workaround. (Nevertheless, as I said, Perl script works fine...) -- Thanks, Roman
My System Specs

09-11-2006	#5 (permalink)
klumsy@xtra.co.nz Guest n/a posts	Re: COM. Can't open an Excel workbook > (Nevertheless, as I said, Perl script works fine...) As well all know perl is a great language, and being so, when it has errors, one of its error handling techinques is to automatically search MSDN for answers / workarounds and adapt the code on the fly to accomodate that Karl Prosser (just woke up)
My System Specs

09-12-2006	#6 (permalink)
Roman Kuzmin Guest n/a posts	Re: COM. Can't open an Excel workbook I tried two workarounds suggested at http://support.microsoft.com/default...b;en-us;320369 WORKAROUND 1 Code: (VBasic) Dim oApp As New Excel.Application() oApp.Visible = True oApp.UserControl = True Dim oBooks As Object = oApp.Workbooks Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US") oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod, Nothing, oBooks, Nothing, ci) It requires getting a type of Workbooks property. This way in PowerShell gives me an error: PS> $xl = New-Object -COM Excel.Application PS> $xl.Workbooks.GetType() Method invocation failed because [System.__ComObject] doesn't contain a method named 'GetType'. At line:1 char:22 + $xl.Workbooks.GetType( <<<< ) Any ideas how to solve this problem and still use this workaround? WORKAROUND 2 Code: (VBasic) Dim oApp As New Excel.Application() oApp.Visible = True oApp.UserControl = True Dim oldCI As System.Globalization.CultureInfo = _ System.Threading.Thread.CurrentThread.CurrentCulture System.Threading.Thread.CurrentThread.CurrentCulture = _ New System.Globalization.CultureInfo("en-US") oApp.Workbooks.Add() System.Threading.Thread.CurrentThread.CurrentCulture = oldCI It turned out that I just can't change the current culture in this way. See the thread "How do I explicitly set the CurrentCulture?" CONCLUSION Both known workarounds do not work in PowerShell. Thus, automation of MS Excel in PowerShell looks problematic for users with en-US Excel but non en-US current locale. Any ideas are welcome. -- Thanks, Roman
My System Specs

09-12-2006	#7 (permalink)
=?Utf-8?B?L1wvXG9cL1wvIFtNVlBd?= Guest n/a posts	Re: COM. Can't open an Excel workbook "Roman Kuzmin" wrote: > I tried two workarounds suggested at > http://support.microsoft.com/default...b;en-us;320369 > > WORKAROUND 1 > > Code: (VBasic) > Dim oApp As New Excel.Application() > oApp.Visible = True > oApp.UserControl = True > Dim oBooks As Object = oApp.Workbooks > Dim ci As System.Globalization.CultureInfo = New > System.Globalization.CultureInfo("en-US") > oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod, > Nothing, oBooks, Nothing, ci) > > > It requires getting a type of Workbooks property. This way in PowerShell > gives me an error: > > PS> $xl = New-Object -COM Excel.Application > PS> $xl.Workbooks.GetType() > Method invocation failed because [System.__ComObject] doesn't contain a > method named 'GetType'. > At line:1 char:22 > + $xl.Workbooks.GetType( <<<< ) > > Any ideas how to solve this problem and still use this workaround? > > WORKAROUND 2 > > Code: (VBasic) > Dim oApp As New Excel.Application() > oApp.Visible = True > oApp.UserControl = True > Dim oldCI As System.Globalization.CultureInfo = _ > System.Threading.Thread.CurrentThread.CurrentCulture > System.Threading.Thread.CurrentThread.CurrentCulture = _ > New System.Globalization.CultureInfo("en-US") > oApp.Workbooks.Add() > System.Threading.Thread.CurrentThread.CurrentCulture = oldCI > > > It turned out that I just can't change the current culture in this way. See > the thread "How do I explicitly set the CurrentCulture?" > > CONCLUSION > > Both known workarounds do not work in PowerShell. Thus, automation of MS > Excel in PowerShell looks problematic for users with en-US Excel but non > en-US current locale. Any ideas are welcome. > > -- > Thanks, > Roman > > >
My System Specs

09-12-2006	#8 (permalink)
=?Utf-8?B?L1wvXG9cL1wvIFtNVlBd?= Guest n/a posts	Re: COM. Can't open an Excel workbook Resend , as code was lost : You need to use PSBase : MowPS>$excel.workbooks.add() Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))" At line:1 char:21 # Working Code : $excel = New-object -com Excel.Application $ci = [System.Globalization.CultureInfo]'en-us' $excel.workbooks.psbase.gettype().InvokeMember("Add", [Reflection.BindingFlags]::InvokeMethod, $null, $excel.workbooks, $null, $ci) MowPS>$excel.workbooks.item('book1') Application : System.__ComObject Creator : 1480803660 Parent : System.__ComObject "/\/\o\/\/ [MVP]" wrote: > > > "Roman Kuzmin" wrote: > > > I tried two workarounds suggested at > > http://support.microsoft.com/default...b;en-us;320369 > > > > WORKAROUND 1 > > > > Code: (VBasic) > > Dim oApp As New Excel.Application() > > oApp.Visible = True > > oApp.UserControl = True > > Dim oBooks As Object = oApp.Workbooks > > Dim ci As System.Globalization.CultureInfo = New > > System.Globalization.CultureInfo("en-US") > > oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod, > > Nothing, oBooks, Nothing, ci) > > > > > > It requires getting a type of Workbooks property. This way in PowerShell > > gives me an error: > > > > PS> $xl = New-Object -COM Excel.Application > > PS> $xl.Workbooks.GetType() > > Method invocation failed because [System.__ComObject] doesn't contain a > > method named 'GetType'. > > At line:1 char:22 > > + $xl.Workbooks.GetType( <<<< ) > > > > Any ideas how to solve this problem and still use this workaround? > > > > WORKAROUND 2 > > > > Code: (VBasic) > > Dim oApp As New Excel.Application() > > oApp.Visible = True > > oApp.UserControl = True > > Dim oldCI As System.Globalization.CultureInfo = _ > > System.Threading.Thread.CurrentThread.CurrentCulture > > System.Threading.Thread.CurrentThread.CurrentCulture = _ > > New System.Globalization.CultureInfo("en-US") > > oApp.Workbooks.Add() > > System.Threading.Thread.CurrentThread.CurrentCulture = oldCI > > > > > > It turned out that I just can't change the current culture in this way. See > > the thread "How do I explicitly set the CurrentCulture?" > > > > CONCLUSION > > > > Both known workarounds do not work in PowerShell. Thus, automation of MS > > Excel in PowerShell looks problematic for users with en-US Excel but non > > en-US current locale. Any ideas are welcome. > > > > -- > > Thanks, > > Roman > > > > > >
My System Specs

02-20-2007	#10 (permalink)
Mike Holden (Mike.Holden@cardium.co.uk) Guest n/a posts	Re: COM. Can't open an Excel workbook Hi, there's another workaround, which I think highlights the 'early' status of PowerShell. If you change your regional settings in Control Panel to English-U.S, scripts seem to work as Microsoft Script Center suggests they should. I'm sure the next service release of PowerShell will cater for the fact that not every user is US-based! Cheers, Mike "Roman Kuzmin" wrote: > /\/\o\/\/, thanks! > > The task was to open in Excel a file $src and save it as $res using another > Excel format $fmt. The working code now is: > > Code: > > $excel = New-Object -COM Excel.Application > $ci = [System.Globalization.CultureInfo]'en-US' > $book = $excel.Workbooks.PSBase.GetType().InvokeMember( > 'Open', [Reflection.BindingFlags]::InvokeMethod, $null, > $excel.Workbooks, $src, $ci) > [void]$book.PSBase.GetType().InvokeMember( > 'SaveAs', [Reflection.BindingFlags]::InvokeMethod, $null, $book, ($res, > $fmt), $ci) > [void]$book.PSBase.GetType().InvokeMember( > 'Close', [Reflection.BindingFlags]::InvokeMethod, $null, $book, 0, $ci) > > > > It looks pretty ugly, but it is the only working way I know now. > > -- > Thanks, > Roman >
My System Specs

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel and Powershell Open Workbook with Multiple Sheets	OldDog	PowerShell	12	08-26-2008 02:48 PM
How To Get Excel File Folder to Associate with Excel File Open	ncprius	Vista file management	1	04-07-2008 08:38 AM
Vista ASP.NET 2.0 Cannot open Excel File using Microsoft.Excel 12.0 COM object	ckellywilson	Microsoft Office	0	03-26-2008 02:04 PM
cant open attachment from excel	julena	Vista mail	1	05-23-2007 12:17 PM
Excel 2007 wil not open and Excel 2007 document in Vista?	lethag	Vista General	0	04-26-2007 03:34 PM