Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    41

    Unanswered: TransferSpreadsheet and password protected spreadsheet

    Using in Access 2003 (no Service Packs)... I am trying to use a module to import a spreadsheet which should overwrite an existing table.

    Before I got into the details, I tried using DoCmd.TransferSpreadsheet to import the file. Access immediately returned "Could not decrypt file". The file does require a password. And I have the password. But I don't know where to place it in the code:

    DoCmd.TransferSpreadsheet acImport, 8, "tblAL_1", "\\....UNC....\al.xls", True, ""

    Also, is it possible to prompt the user for the password rather than hardcode it?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I'll have a stab:
    Code:
    Sub ImportExcel_Pass()
     
        Dim exApp As Excel.Application
        Dim exWB As Excel.Workbook
        Dim Password As String
        
        Password = InputBox("Please enter the password")
        
        Set exApp = New Excel.Application
        Set exWB = exApp.Workbooks.Open("C:\Stuff.xls")
        
        exWB.Unprotect Password
        exWB.Save
        
        exWB.Close
        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet1", "C:\Stuff.xls"
        
        Set exWB = exApp.Workbooks.Open("C:\Stuff.xls")
        
        exWB.Protect Password
        
        exApp.Quit
        
        Set exWB = Nothing
        Set exApp = Nothing
     
    End Sub
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    41
    So close... I had to make a small change:

    Code:
    exWB.Unprotect Password
    exWB.Password = ""
    exWB.Save
    That removes the password!

    Replacing
    Code:
     exWB.Protect Password
    with obvious differences, restores it!


    Quote Originally Posted by pootle flump
    Hi

    I'll have a stab:
    Code:
    Sub ImportExcel_Pass()
     
        Dim exApp As Excel.Application
        Dim exWB As Excel.Workbook
        Dim Password As String
        
        Password = InputBox("Please enter the password")
        
        Set exApp = New Excel.Application
        Set exWB = exApp.Workbooks.Open("C:\Stuff.xls")
        
        exWB.Unprotect Password
        exWB.Save
        
        exWB.Close
        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Sheet1", "C:\Stuff.xls"
        
        Set exWB = exApp.Workbooks.Open("C:\Stuff.xls")
        
        exWB.Protect Password
        
        exApp.Quit
        
        Set exWB = Nothing
        Set exApp = Nothing
     
    End Sub
    HTH

Posting Permissions

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