Results 1 to 4 of 4
  1. #1
    ragsgold is offline Access & Excel Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51

    Refresh Password protected excel from access

    Hi All,



    I have an excel file with table data and a pivot that is linked to one of the tables in my access file. this excel file is password protected. when I run a access macro or a VBA (which ever you suggest) it should open the excel file automatically feed in the password, refresh the macro in excel (there is a macro in excel to refresh work book) ;save the excel file password protect it and close the excel file.

    Any suggestions ? I am fine with VBA or non VBA solutions like RunApp macro command lineetc

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,028
    Google: access vba refresh Excel password
    Does this help:
    http://www.access-programmers.co.uk/...d.php?t=171000
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ragsgold is offline Access & Excel Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2009
    Location
    Denver,CO
    Posts
    51
    Tried the below code in your link - i get error for every linke I am not good at coding for sure i am making a mistake some where. could you please guide me as to where i can input file path and password ?

    Sub OpenExcel(xlPath As String, password As String)
    'xlPath is the path for the Excel file as a string. Ex. "C:\Book1.xls"
    'password is the password for the workbook as a string. Ex. "mypassword"

    On Error Resume Next

    'Error if Excel app not currently open:
    Const ERR_EXCEL_NOT_OPEN As Long = 429

    'Excel Variables:
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook

    'Open Excel Application
    '===============================================
    Set xlApp = GetObject(, "Excel.Application")

    If Err.Number = ERR_EXCEL_NOT_OPEN Then

    'If Excel not open, open new instance:
    Set xlApp = New Excel.Application

    End If

    xlApp.Visible = True

    'Open workbook:
    Set xlWb = xlApp.Workbooks.Open(xlPath, , , , password)

    'Refresh all pivot tables and save:
    With xlWb
    .RefreshAll
    .Save
    '.Close 'Remove comment to close workbook once refreshed
    End With

    'Clean variables:
    Set xlWb = Nothing
    Set xlApp = Nothing

    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,028
    How are you executing the procedure? A button click? If you are calling the sub from Click event, something like:


    OpenExcel("the path string for your excel file", "the excel file password")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Password protected form or report
    By mar_t in forum Access
    Replies: 11
    Last Post: 06-20-2013, 05:52 AM
  2. Replies: 2
    Last Post: 09-21-2012, 05:12 PM
  3. Open a password protected MDB/MDW
    By abcc14 in forum Security
    Replies: 8
    Last Post: 11-02-2011, 07:41 AM
  4. Replies: 4
    Last Post: 09-14-2011, 12:33 AM
  5. Password protected Switcboard buttons
    By mar_t in forum Access
    Replies: 9
    Last Post: 07-23-2011, 08:13 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums