open excel sheet vba

[cmz21]

Hello,
How can I open a password protected excel using vba. Can this be done using the GetObject method? I was using xlApp.Workbooks.open, but would like to try using GetObject.

Thanks

 

[Enkay62]

Hi.
Assuming that you intend to run Your code from a standard excel file, if you want to open another excel file called, say, "Myfile.xls" which has 11111111 as password, this is the code.
Workbooks.Open Filename:="Myfile.xls, password:="11111111"
Hope this helps.
Bye
Nick

 

[cmz21]

Hello,
I am running the code from an Access Macro. I have tried using variations of the following code -


Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open FileName:="c:filepath\filename", Password:="whatever"
Set xlBook = xlApp.Workbooks.Add
Set xlSheet_MetLife = xlBook.Worksheets.Add

This code creates a new workbook instead of using the other workbook. If I omit the createobject line, it gives me an object variable not set error.

I have also tried -


Set xlApp = GetObject("c:\filepath\filename.xls")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet_MetLife = xlBook.Worksheets.Add


but this won't let me set the password.

Any suggestions would be appreciated.

cmz






'Set xlApp = GetObject("c:\filepath\filename.xls")
xlApp.Workbooks.Open FileName:="c:filepath\filename", Password:="whatever"
Set xlBook = xlApp.Workbooks.Add
'Set xlBook = xlApp.Workbooks.Open FileName:="c:\filepath\filename.xls", Password:="whatever"
Set xlSheet_MetLife = xlBook.Worksheets.Add

 

[cmz21]

I was able to get it working using the following code. The only problem I have now is killing all objects. I seem to have one which I cannot identify that keeps an instance of Excel running in the background.

Thanks for all help



Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = Workbooks.Open(FileName:="c:\filepath\filename", Password:="password")
Set xlSheet = xlBook.Worksheets.Add

 

[xlbo]

so have you set xlApp to nothing ??



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[cmz21]

I figure out the problem - some of my objects were not fully qualified.

Thanks for your help.

cmz