Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    5

    Unhappy Unanswered: import data from a protected excel workbook?

    hi,

    is it true that we can't import data from a protected excel workbook?

    many thanks in advance!

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: import data from a protected excel workbook?

    you can do it, if the Excel sheet is opened in Excel....

    so you can open it in Excel manually and then link or import your Excel Data or you can do it using VBA....


    Set myExcelApp = CreateObject("excel.application")
    myExcelApp.Workbooks.Open FileName:="E:\My Documents\My Desktop\Book1.xls", ReadOnly:=True, Password:="test", WriteResPassword:="test"
    myExcelApp.Visible = False


    ' here import your data ....
    ' you can use myExcelApp.Quit when you close your MDB application....
    myExcelApp.Quit




    note: I tested this in ExcelXP (strong encryption) and AccessXP.

    jiri
    Last edited by playernovis; 01-26-03 at 13:33.

  3. #3
    Join Date
    Jan 2003
    Posts
    5
    you mean i 1st unprotect the excel workbook before I import the data to Access?

    I tried to include the above code in the Access macro, and import the data but received the error:

    "The wizard is unable to access the information in the file 'c:\test.xls'. Please check that the file exists and is in the correct format.'

    (error not encountered when workbook is not protected.)

    Many thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    I used both my ways again and it works....

    if it is one time thing, open workbook in Excel, keep it open and then go to access and try to import it or link it.


    jiri

  5. #5
    Join Date
    Jan 2003
    Posts
    5
    is your protection at workbook or worksheet level? or both? thanks!

  6. #6
    Join Date
    Jan 2003
    Posts
    5
    it works when i tried to open workbook in Excel, keep it open and imported the data.

    but when i tried using the macro method, it failed..

    may i know y?

    Sub test()

    Set myExcelApp = CreateObject("excel.application")
    myExcelApp.Workbooks.Open FileName:="c:\samples.xls", ReadOnly:=True, Password:="test", WriteResPassword:="test"
    myExcelApp.Visible = false

    End Sub

  7. #7
    Join Date
    Jan 2003
    Posts
    5
    the macro method works if i do not set the readonly property to TRUE. may i know y?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •