UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Error Handler, Access 2016    
 
   
aggiemarine07
post Aug 13 2019, 09:18 AM
Post#1



Posts: 48
Joined: 7-January 11



So I was able to modify some code I found on the internet to do what I need it to: 1) open all workbooks in a folder, 2) copy a spreadsheet from a master workbook into these workbooks, and 3) finally save/close the file (all while taking into account if the workbook has a password) .

Problem is that within the 500 workbooks that its going through, someone put in a weird password. I then attempted to put in an error handler to give me a msgbox of the name of the workbook but I cant seem to get it working correctly. Please see the below code and let me know what I am missing (I've probably just been staring at it for too long and forgot something stupid).

It Error's out on the
CODE
With xl.Workbooks.Open
line within the below code. Thanks in advance.

CODE
Sub Command0_Click()
Dim xl As Excel.Application
Dim wbMaster As Excel.Workbook
Set xl = New Excel.Application
Set wbMaster = xl.Workbooks.Open(CurrentProject.path & "\ProjectBabelfish")

Dim wsCopy As Excel.Worksheet
Set wsCopy = wbMaster.Worksheets("Babelfish")

Dim sFolderPath As String
sFolderPath = wbMaster.path & "\PLOGs\"
If Right(sFolderPath, 1) <> "\" Then sFolderPath = sFolderPath & "\"

Dim sFileName As String
sFileName = Dir(sFolderPath & "*.xlsx")

'Disable screenupdating (to prevent "screen flickering" so macro runs smoother)
'Disable alerts (to suppress "Are you sure?" prompts during worksheet deletion)
xl.ScreenUpdating = False
xl.DisplayAlerts = False

'Begin loop through files in the folder
Do While Len(sFileName) > 0

    Dim sWBOpenPassword As String
    Dim sWBProtectPassword As String
    Select Case sFileName
        'Specify workbook names that require passwords here
        Case "book1.xlsx"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

        'If different books require different passwords, can specify additional names with their unique passwords
        Case "Book3.xlsx"
            sWBOpenPassword = "book3openpassword"
            sWBProtectPassword = "book3protectionpassword"

        'Keep specifying excel file names and their passwords until completed
        Case "Book10.xlsx", "Book257.xlsx"
            sWBOpenPassword = "GenericOpenPW2"
            sWBProtectPassword = "GenericProtectPW2"

        'Case Else will handle the remaining workbooks that don't require passwords
        Case Else
            'MsgBox sFileName & " has a wierd password"
            sWBOpenPassword = "550"
            sWBProtectPassword = "550"

    End Select

    'Open file using password (if any)
    With xl.Workbooks.Open(sFolderPath & sFileName, UpdateLinks:=False, Password:=sWBOpenPassword)
        If Err.Number = 1004 Then MsgBox "The workbook " & sFileName & " is at a middle school dance."
        Dim bProtectedWB As Boolean
        bProtectedWB = False    'Reset protected wb check to false

        'Check if workbook is protected and if so unprotect it using the specified protection password
        If .ProtectStructure = True Then bProtectedWB = True
        If bProtectedWB = True Then .Unprotect sWBProtectPassword
            .Sheets(1).Name = "Whole Foods Market PLOG"
            
        On Error Resume Next    'Suppress error if copied worksheet does not yet exist
        .Worksheets(wsCopy.Name).Delete 'Delete existing sheet if it exists
        On Error GoTo 0         'Remove "On Error Resume Next" condition
    
            wsCopy.Copy After:=.Worksheets(.Worksheets.Count)   'Copy template into the workbook
            .Worksheets(wsCopy.Name).Cells.Replace wbMaster.Name, .Name 'Change references from master workbook to current workbook
    
            'If workbook was protected, reprotect it with same protection password
            If bProtectedWB = True Then .Protect sWBProtectPassword
    
            'Close file and save the changes
        
        If Not IsEmpty(varLinks) Then
            For i = 1 To UBound(varLinks)
                .BreakLink _
                    Name:=varLinks(i), _
                    Type:=xlLinkTypeExcelLinks
            Next i
        End If
        
        .Close True

    End With

    sFileName = Dir 'Advance to next file in the folder
Loop

'Re-enable screenupdating and alerts
xl.ScreenUpdating = True
xl.DisplayAlerts = True
xl.Quit

MsgBox "Added Tab to all PLOG files"

This post has been edited by aggiemarine07: Aug 13 2019, 09:19 AM
Go to the top of the page
 
 
Start new topic
Replies
theDBguy
post Aug 13 2019, 10:46 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,316
Joined: 19-June 07
From: SunnySandyEggo


Hi. I don't see any error handler in your code. Did you say you added one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
aggiemarine07
post Aug 13 2019, 11:42 AM
Post#3



Posts: 48
Joined: 7-January 11



@theDBguy less of handler and more of an IF statement, its right below the Workbooks.Open portion

CODE
If Err.Number = 1004 Then MsgBox "The workbook " & sFileName & " is at a middle school dance."



*the phrasing was my attempt at humor because it means that the workbook is awkward/odd (like a middle school dance) because it has a different password smile.gif
Go to the top of the page
 
theDBguy
post Aug 13 2019, 11:47 AM
Post#4


Access Wiki and Forums Moderator
Posts: 76,316
Joined: 19-June 07
From: SunnySandyEggo


Hi. Unfortunately, without an error handler, that line is bypassed by the error message. In other words, Access will never read that line in case of an error unless you told it to beforehand. For example, just before the Workbooks.Open, add an On Error Resume Next line. However, this is really not the recommended was to handle errors. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
aggiemarine07
post Aug 14 2019, 04:36 PM
Post#5



Posts: 48
Joined: 7-January 11



@theDBguy worked like a charm; thanks!

I just needed a quick/simple solution to figure out which file was causing the problem because it's a one off problem that'll never happen again.
Go to the top of the page
 
theDBguy
post Aug 14 2019, 04:45 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,316
Joined: 19-June 07
From: SunnySandyEggo


Hi. To properly figure out an issue, you should trap the error or let the code error out, so you can go to debug mode. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    21st September 2019 - 01:57 AM