Open password protected Excel workbook in Visual Basic

Hello,

My VB 6.0 application read and writes data from and to a MS Excel workbook,
using the Microsoft.Jet.OLEDB.4.0 provider.

Now I want to protect the Excel workbook with a password, but I figured out
that it is not possible to open the workbook for data access with ADO
(http://support.microsoft.com/?KBID=211378).

Is there another way to use a password protected Excel workbook in my
application?

Regards,

Geert-Pieter

re: Open password protected Excel workbook in Visual Basic

If you know the password, you should be able to use it via the the MS Excel
Object Model.

Here's a link to the 2000 documentation
http://msdn.microsoft.com/library/en...tmodelguide.as
p?frame=true
http://msdn.microsoft.com/library/de...us/odeomg/html
/deovrmicrosoftexcel2000.asp

Good luck,
--
John MacIntyre
VC++ / VB / ASP / Database Developer
http://www.johnmacintyre.ca


"Geert-Pieter Hof" <g.p.m.hof@wbmt.tudelft.nl.blablabla> wrote in message
news:bj7m4d$kp1$1@azure.qinip.net...[color=blue]
> Hello,
>
> My VB 6.0 application read and writes data from and to a MS Excel[/color]
workbook,[color=blue]
> using the Microsoft.Jet.OLEDB.4.0 provider.
>
> Now I want to protect the Excel workbook with a password, but I figured[/color]
out[color=blue]
> that it is not possible to open the workbook for data access with ADO
> (http://support.microsoft.com/?KBID=211378).
>
> Is there another way to use a password protected Excel workbook in my
> application?
>
> Regards,
>
> Geert-Pieter
>
>[/color]

re: Open password protected Excel workbook in Visual Basic

According to MS Knowledge Base Article 249843, the MS Excel Object library
is not redistributable. So I cannot the mentioned solution on computers that
haven't got MS Excel installed.

So, is there another way to read data from password protected Excel
workbooks?

Best regards,

Geert-Pieter

"John MacIntyre" <Please@reply.to.group.thx> schreef in bericht
news:whR5b.20469$su.516344@news20.bellglobal.com.. .[color=blue]
> If you know the password, you should be able to use it via the the MS[/color]
Excel Object Model.[color=blue]
>
>
> "Geert-Pieter Hof" <g.p.m.hof@wbmt.tudelft.nl.blablabla> wrote in message
> news:bj7m4d$kp1$1@azure.qinip.net...[color=green]
> > Hello,
> >
> > My VB 6.0 application read and writes data from and to a MS Excel[/color][/color]
workbook,[color=blue][color=green]
> > using the Microsoft.Jet.OLEDB.4.0 provider.
> >
> > Now I want to protect the Excel workbook with a password, but I figured[/color][/color]
out[color=blue][color=green]
> > that it is not possible to open the workbook for data access with ADO
> > (http://support.microsoft.com/?KBID=211378).
> >
> > Is there another way to use a password protected Excel workbook in my
> > application?
> >
> > Regards,
> >
> > Geert-Pieter[/color][/color]

re: Open password protected Excel workbook in Visual Basic

"Geert-Pieter Hof" <g.p.m.hof@wbmt.tudelft.nl.blablabla> wrote in message
news:bjkklf$j97$1@azure.qinip.net...[color=blue]
> According to MS Knowledge Base Article 249843, the MS Excel Object library
> is not redistributable. So I cannot the mentioned solution on computers[/color]
that[color=blue]
> haven't got MS Excel installed.
>
> So, is there another way to read data from password protected Excel
> workbooks?
>
> Best regards,
>
> Geert-Pieter[/color]

Hi Geert-Pieter,

I assumed the user would have Excel.

There is also a ODBC driver available. This has never really appealed to me
since Excel does not have a rigid table structure, so I've always gone
through the Object Model.

Also, you better check the dependencies ... who knows if the user needs to
have XL on their PC for the ODBC drivers to work.

Do you mind if I ask why you are using an Excel file? I am assuming, from
comments in this thread, the XL file is yours as part of your distributable.

Regards,
John MacIntyre
VC++ / VB / ASP / Database Developer
http://www.johnmacintyre.ca

re: Open password protected Excel workbook in Visual Basic

> There is also a ODBC driver available. This has never really appealed to
me[color=blue]
> since Excel does not have a rigid table structure, so I've always gone
> through the Object Model.[/color]

Now I'm becoming a bit confused: MS Knowledge Base Article 211378 states
that opening an password protected Excel workbook is not possible when using
the MS Excel ODBC driver, so the above suggestion won't work.

However, I am using the MS Jet OLE DB driver and I also can't open a
password protected Excel workbook, although this problem isn't mentioned in
MS Knowledge Base Article 211378. The MS Excel ODBC driver and the MS Jet
OLE DB driver are two different drivers, don't you think?
[color=blue]
> Also, you better check the dependencies ... who knows if the user needs to
> have XL on their PC for the ODBC drivers to work.
>
> Do you mind if I ask why you are using an Excel file? I am assuming, from
> comments in this thread, the XL file is yours as part of your[/color]
distributable.

My application simulates some stuff and needs measured data that is spread
over about 30 worksheets with about 2000 measurements pro worksheet. When
some measurements change, new worksheets are supplied. So I don't want to
convert the worksheets to a database tables each time some data changes.
Furthermore, the people that are going to work with the software know to
handle Excel workbooks, but hardly work with Acces databases.

Best regards,

Geert-Pieter

re: Open password protected Excel workbook in Visual Basic

Maybe you are misreading the Excel licensing issue. If your users have Excel,
then they have the complete object model, as fully licensed as the rest of their
Excel. (You can develop and run an entire application in VBA for Excel in any
standard installation).

I don't think you can get around the password protection through any driver. It
wouldn't be much of a password protection if you could.

I think you should go ahead and go in through the object model. I have done it
several times, never redistributed any part of Excel, and never had complaints
that the program wouldn't run, as long as the user already had Excel installed.
(Had other complaints, of course, but that is programming).

"Geert-Pieter Hof" <g.p.m.hof@wbmt.tudelft.nl.blablabla> wrote in message
news:bjmod4$b3c$1@azure.qinip.net...[color=blue][color=green]
> > There is also a ODBC driver available. This has never really appealed to[/color]
> me[color=green]
> > since Excel does not have a rigid table structure, so I've always gone
> > through the Object Model.[/color]
>
> Now I'm becoming a bit confused: MS Knowledge Base Article 211378 states
> that opening an password protected Excel workbook is not possible when using
> the MS Excel ODBC driver, so the above suggestion won't work.
>
> However, I am using the MS Jet OLE DB driver and I also can't open a
> password protected Excel workbook, although this problem isn't mentioned in
> MS Knowledge Base Article 211378. The MS Excel ODBC driver and the MS Jet
> OLE DB driver are two different drivers, don't you think?
>[/color]
[color=blue][color=green]
> > Also, you better check the dependencies ... who knows if the user needs to
> > have XL on their PC for the ODBC drivers to work.
> >
> > Do you mind if I ask why you are using an Excel file? I am assuming, from
> > comments in this thread, the XL file is yours as part of your[/color]
> distributable.
>
> My application simulates some stuff and needs measured data that is spread
> over about 30 worksheets with about 2000 measurements pro worksheet. When
> some measurements change, new worksheets are supplied. So I don't want to
> convert the worksheets to a database tables each time some data changes.
> Furthermore, the people that are going to work with the software know to
> handle Excel workbooks, but hardly work with Acces databases.
>
> Best regards,
>
> Geert-Pieter
>
>[/color]

re: Open password protected Excel workbook in Visual Basic

It's not a licensing issue, it's encryption. Password protecting an
Excel workbook encrypts its contents on disk and ADO has no way of
decrypting it.

"Steve Gerrard" <notstevegerrard@comcast.net> wrote in message news:<1pudndzHZaWDdcKiXTWJkw@comcast.com>...[color=blue]
> Maybe you are misreading the Excel licensing issue. If your users have Excel,
> then they have the complete object model, as fully licensed as the rest of their
> Excel. (You can develop and run an entire application in VBA for Excel in any
> standard installation).
>
> I don't think you can get around the password protection through any driver. It
> wouldn't be much of a password protection if you could.
>
> I think you should go ahead and go in through the object model. I have done it
> several times, never redistributed any part of Excel, and never had complaints
> that the program wouldn't run, as long as the user already had Excel installed.
> (Had other complaints, of course, but that is programming).
>
> "Geert-Pieter Hof" <g.p.m.hof@wbmt.tudelft.nl.blablabla> wrote in message
> news:bjmod4$b3c$1@azure.qinip.net...[color=green][color=darkred]
> > > There is also a ODBC driver available. This has never really appealed to[/color][/color]
> me[color=green][color=darkred]
> > > since Excel does not have a rigid table structure, so I've always gone
> > > through the Object Model.[/color]
> >
> > Now I'm becoming a bit confused: MS Knowledge Base Article 211378 states
> > that opening an password protected Excel workbook is not possible when using
> > the MS Excel ODBC driver, so the above suggestion won't work.
> >
> > However, I am using the MS Jet OLE DB driver and I also can't open a
> > password protected Excel workbook, although this problem isn't mentioned in
> > MS Knowledge Base Article 211378. The MS Excel ODBC driver and the MS Jet
> > OLE DB driver are two different drivers, don't you think?
> >[/color]
>[color=green][color=darkred]
> > > Also, you better check the dependencies ... who knows if the user needs to
> > > have XL on their PC for the ODBC drivers to work.
> > >
> > > Do you mind if I ask why you are using an Excel file? I am assuming, from
> > > comments in this thread, the XL file is yours as part of your[/color]
> > distributable.
> >
> > My application simulates some stuff and needs measured data that is spread
> > over about 30 worksheets with about 2000 measurements pro worksheet. When
> > some measurements change, new worksheets are supplied. So I don't want to
> > convert the worksheets to a database tables each time some data changes.
> > Furthermore, the people that are going to work with the software know to
> > handle Excel workbooks, but hardly work with Acces databases.
> >
> > Best regards,
> >
> > Geert-Pieter
> >
> >[/color][/color]