Link to home
Start Free TrialLog in
Avatar of mvarner2000

asked on

Select and import a password protected excel file.

I have an access database that the user imports imports excel spreadsheets into each week.  There are about 20 excel files in different directories and they are all password protected.  

Does anyone have the VB code or a macro that can be used to automate this process?


Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hi Marshall,

TransferSpreadsheet will bomb on a password-protected workbook.

I would set thing up in a loop, and before importing the data opening each workbook, removing the password, running
TransferSpreadsheet, and then reapplying the password.  This shows how to process one file:

Sub test()

    Dim xlApp As Object
    Dim xlWb As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(FileName:="C:\folder\test.xls", Password:="test")
    xlWb.Password = ""
    xlWb.Close True
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "C:\folder\test.xls", True

    Set xlWb = xlApp.Workbooks.Open(FileName:="C:\folder\test.xls")
    xlWb.Password = "test"
    xlWb.Close True

    Set xlWb = Nothing

    Set xlApp = Nothing

End Sub

This can be extended to do what you need, if you provide some more details.


There's quite a lot to this.
You're going to have to use Excel automation. (VBA code)
You're going to have to build an interface (form) to kick this process off
you'll need to know the directories and create a way to browse for them. then store the path's
you're going to have to build a table to house all the Excel Passwords to pass to your excel automation
You're going to have to define all the tables and fields to store the incoming data
You'll need to probably build a temp table, and set of queries to put the imported data into your main table

This is a good think you have enough info at hand to start all this? What's your knowledge level?
Avatar of mvarner2000


Thanks Guys

I hope this will get me started.  Looks like the hardest part is going to be building a form that will allow me to browse for the spread sheets I need to import.  The password is going to be the same for all the files so I should b e able to hardcode it somehow.

I have worked a great deal with Access but I am an advanced novis in VB.  I will look this over this weekend and let you know what I come up with.


If the files will always be in the same directories with the same names, then I would set up a table:


Then open an ADO or DAO recordset and use the values to power TransferSpreadsheet.

The files will be in multiple directories which could change but not to often but the file names are date specific and change each week.

Are all the files the same layout? Same field names and types? You might start with going into one of your spreadsheets and unlocking it then saving and closing. You can put the password protection back in once you're done. Use Access's Link Table function to link the Excel Spreadsheet so that it appears in your database as a table. You can then use a MakeTable Query to create the Table you're going to need to populate when you use the Transfer Spreadsheet method. Once the table is created, you can delete the linked excel table. This will give you a starting point for your import and give you some text to play around with to make sure all your data types in your table are correct.
I'm pasting in some code you can copy and paste into a module. This code will work with your form to allow you to browse for your files. There is one place you'll need to make a change, and I commented it in bold. First is to create a form Call it whatever you want. Put a button on the form. This button will be the FunctionCall for the function. Create a text box on your form and call it txtPathFile. When you push the button, it will open a browser window. Go find your file and click ok (or save..whatever) and it puts the full path into the text box. Then you can use the value in the text box into your transfer spreadsheet code.

On your button's On_Click event, paste this


Next, Paste this into a module
Global strExportPathAndFileName As String
' GetOpenFileName Declaration
    Declare Function Open_File_Name_Dialog_Box Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OpenFilename) As Long
' OpenFileName Structure
    Type OpenFilename
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        Flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type

Public Function OpenDialogBox(strSource As String)
' Enable error handler for this routine
    On Error GoTo err_OpenDialogBox
' Dimension a variable for the return value of GetOpenFileName
    Dim ReturnValue As Long
    Dim strFullPath As String
    Dim lnImportType As Long
' Dimension a Variable Of the OPENFILENAME type
    Dim Open_File_Name_Structure As OpenFilename
' Dimension variables used for filters
    Dim strFilter1 As String, strFilter2 As String, Filter As String, strIDir As String
' Set the filters
    strFilter1 = "Select the CLICK ME File(*.txt)" + Chr$(0) + "*.txt" + Chr$(0)
    strFilter2 = "All files (*.*)" + Chr$(0) + "*.*" + Chr$(0)
    Filter = strFilter1 + strFilter2 + Chr$(0)
' Assign the structure members
        Open_File_Name_Structure.lStructSize = Len(Open_File_Name_Structure)
        Open_File_Name_Structure.hwndOwner = Screen.ActiveForm.hWnd
        Open_File_Name_Structure.lpstrFilter = Filter
        Open_File_Name_Structure.nFilterIndex = 1
        Open_File_Name_Structure.lpstrFile = Chr$(0) & Space$(255) & Chr$(0)
        Open_File_Name_Structure.nMaxFile = 10000
        Open_File_Name_Structure.lpstrFileTitle = Chr$(0) & Space$(255) & Chr$(0)
        Open_File_Name_Structure.nMaxFileTitle = 10000
        Open_File_Name_Structure.lpstrDefExt = ".xls" & Chr$(0)
        Open_File_Name_Structure.lpstrInitialDir = CurDir$ + Chr$(0)
        Open_File_Name_Structure.nFileOffset = 0
        Open_File_Name_Structure.nFileExtension = 0
        Open_File_Name_Structure.lpstrTitle = "Select a File"
' Call the function to open the DialogBox
    ReturnValue = Open_File_Name_Dialog_Box(Open_File_Name_Structure)
' Put the Path and File Name onto the form
    strFullPath = Left(Open_File_Name_Structure.lpstrFile, InStr(Open_File_Name_Structure.lpstrFile, Chr$(0)) - 1)
    Select Case strSource
    Case "txtPathFile"
        Forms!YOURFORM(strSource).Value = strFullPath '<-----------------------------WILL NEED YOUR FORM'S NAME HERE
    End Select
' Exit the Function
    Exit Function
' Error handler for this routine
    Select Case Err
        Case 3315
            Exit Function
        Case Else
            MsgBox "Function modOpen_Dialog_Box_Code.OpenDialogBox." & vbCrLf & "You have error number " & Err & ".  " & Err.Description
    End Select
End Function

You can play with this for a bit until you get comfortable. I left a select statement where you have to change your Form's name in case you wanted to use this function from a different form or a different button. Let me or Patrich know if you have any questions so far.

> The files will be in multiple directories which could change but not to often but the file names are
> date specific and change each week.

Then my approach should still work--we just need to be a little more clever :)

Avatar of jefftwilley
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial