Avatar of mvarner2000
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?

Thanks

Marshall
Microsoft Access

Avatar of undefined
Last Comment
jefftwilley

8/22/2022 - Mon
Patrick Matthews

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

    xlApp.Quit
    Set xlApp = Nothing

End Sub

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

Regards,

Patrick
jefftwilley

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 start.....you think you have enough info at hand to start all this? What's your knowledge level?
J
mvarner2000

ASKER
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.


Marshall
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Patrick Matthews

Marshall,

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

tblExcel
--------------------------------------------------------------------------
PathAndFileName
Password
ImportIntoTable

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

Patrick
mvarner2000

ASKER
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.

Marshall
jefftwilley

Marshall,
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.
J
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jefftwilley

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

=OpenDialogBox("txtPathFile")


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
err_OpenDialogBox:
    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.
J
Patrick Matthews

Marshall,

> 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 :)

Patrick
ASKER CERTIFIED SOLUTION
jefftwilley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question