Transfer password protected sheet

  • Thread starter Thread starter Good Try
  • Start date Start date
G

Good Try

I have an Access application that creates a series of Excel spreadsheets for
my users from a common Excel template. The users use the spreadsheet to
enter data which is then massaged by numerous formulae in the Excel
spreadsheet. When complete, I need to return the results of those formulae
into my Access database. The data is sensitive, so each user has a password
stored in the database. The spreadsheets are password protected using this
value.

To create the spreadsheets, I start MS Excel using a call to
CreateObject("Excel.Application"). I set the UserControl property of the
Excel.Application instance to false. When this routine completes, the
Excel.Application object goes out of scope and Excel terminates properly.

My problem occurs when trying to retrieve the data from Excel into Access.
I was using the TransferSpreadsheet method, but this failed since the
workbooks are all password protected. I then created an instance of Excel
using the same method and settings described above. I use this instance of
Excel to open the file using the password. I then call the
TransferSpreadsheet method (same as before) and my data is imported
properly. Lastly, I use the Excel instance to close the workbook.

For some reason, Excel does not terminate. After a few minutes the machine
locks, requiring me to manually terminate Excel from the task list. I have
tried calling the Quit method on the Excel.Application object. I have
explicitly set my variable to Nothing. I have combined these two. Excel
persists.

I need a solution to import data from an Excel spreadsheet that is password
protected and the password is known. Can anyone suggest how to modify the
above approach and cause Excel to terminate? If this is not the best
approach, can someone suggest a better one given these constraints?

While I have your attention, does anyone know what the useOA parameter of
the TransferSpreadsheet method does (Excel 2000)? I have tried setting this
to true, false and omitting this altogether and cannot see a difference.
Neither can I find any documentation on this option.
--
Kerry Carroll

Please remove the spaces from the following address if you need to reply to
me directly
kerry . carroll @ cinbell . com
 
Hi Kerry,

Last things first: I haven't a clue what the UseOA argument is for
either<g>.

As for getting rid of the Excel instance, are you making quite certain
that there are no workbooks open before you call xlApp.Quit?

Try something like this (from memory)

Do Until xlApp.Workbooks.Count = 0
xlApp.Workbooks(1).Close False
Loop
xlApp.Quit

I have an Access application that creates a series of Excel spreadsheets for
my users from a common Excel template. The users use the spreadsheet to
enter data which is then massaged by numerous formulae in the Excel
spreadsheet. When complete, I need to return the results of those formulae
into my Access database. The data is sensitive, so each user has a password
stored in the database. The spreadsheets are password protected using this
value.

To create the spreadsheets, I start MS Excel using a call to
CreateObject("Excel.Application"). I set the UserControl property of the
Excel.Application instance to false. When this routine completes, the
Excel.Application object goes out of scope and Excel terminates properly.

My problem occurs when trying to retrieve the data from Excel into Access.
I was using the TransferSpreadsheet method, but this failed since the
workbooks are all password protected. I then created an instance of Excel
using the same method and settings described above. I use this instance of
Excel to open the file using the password. I then call the
TransferSpreadsheet method (same as before) and my data is imported
properly. Lastly, I use the Excel instance to close the workbook.

For some reason, Excel does not terminate. After a few minutes the machine
locks, requiring me to manually terminate Excel from the task list. I have
tried calling the Quit method on the Excel.Application object. I have
explicitly set my variable to Nothing. I have combined these two. Excel
persists.

I need a solution to import data from an Excel spreadsheet that is password
protected and the password is known. Can anyone suggest how to modify the
above approach and cause Excel to terminate? If this is not the best
approach, can someone suggest a better one given these constraints?

While I have your attention, does anyone know what the useOA parameter of
the TransferSpreadsheet method does (Excel 2000)? I have tried setting this
to true, false and omitting this altogether and cannot see a difference.
Neither can I find any documentation on this option.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
John,

Thanks for the response. I have stepped through the code and confirm that
the Workbooks.Count property = 0. Still hoping that you or someone else can
propose a solution.

Thanks

--
Kerry Carroll

Please remove the spaces from the following address if you need to reply to
me directly
kerry . carroll @ cinbell . com
 
Back
Top