0

I am trying to achieve the following: I have a workbook that whose job is to loop through a set of input files (from different departments), all of which are supposed to be identical. However, i have noticed that some departments tend to rename their sheets at will. I have code that loops through all files and checks whether a given sheet named "Template" is there or not.

Now, i have a long time to plan for this, but once the input from departments start pouring in, i have very little time to verify everything and import it. One such thing is to implement a "fix errors" function, which, among other things, checks for the presence of the "Template" sheet in the workbook. If it is not there, i want the macro to open the workbook, allow the user (me) to find the correct sheet, manually rename it to "Template", and then save and close. Only then do I want the code to continue to run (checking the next workbook or other errors in this same workbook), in a similar way that happens when a mesage box or file dialog opens.

I have the following for this function for now (part of a larger code, project is a custom class):

If Not project.FileHasSheet Then
    answer = MsgBox("Open file and add sheet?", vbYesNo + vbQuestion)

    If answer = vbYes Then
        Set tempWorkbook = Workbooks.Open(ThisWorkbook.Path & "/Inputfiler/" & project.filename)

        MsgBox ("done")
        tempWorkbook.Close
    Else
        'do something
    End If

The messagebox "done" is going to be removed once this works. The code above just opens the workbook and immediately closes it.

If Not project.FileHasSheet Then
    answer = MsgBox("Open file and add sheet?", vbYesNo + vbQuestion)
    If answer = vbYes Then
        Set tempWorkbook = Workbooks.Open(ThisWorkbook.Path & "/Inputfiler/" & project.filename)
        Do Until IsFileOpen(ThisWorkbook.Path & "/Inputfiler/" & project.filename) = False
            DoEvents        
        Loop
        MsgBox ("done")
        tempWorkbook.Close
    Else
        'do something
    End If

This does not work either. The other workbook opens and the "hourglass" starts spinning, not allowing the user to edit the newly opened workbook. The IsFileOpen function is:

Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        Case Else
            Error errnum
    End Select

End Function
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
gudal
  • 337
  • 3
  • 9
  • 4
    A thought for an alternative solution: would it work to use the sheet code name instead of the caption? You wouldn't have to worry about a sheet being renamed then. If the "Template sheet" is in a different workbook from the code, you could have some code which identifies the correct sheet caption/name from the codename. – Mistella May 10 '19 at 13:20
  • 1
    What about sheet(s) missing -> open template(s) and stop...user fills in template, saves it, hits a continue button to resume or possibly restart the check – James May 10 '19 at 13:21
  • 1
    VBA has no such thing as a "Wait mode", with the exception of showing messages/dialogs that wait for user input. You can use a Timer to start another procedure after a given interval. Or the user needs to start by clicking a button, pressing a keyboard combination or triggering an event of some kind... – Cindy Meister May 10 '19 at 14:16
  • Display a userform `vbModeless` (this allows the user to interact with the environment) and then trigger the "continue" from a button or the form's unload. This probably require some refactoring of your existing code. See [here](https://stackoverflow.com/questions/16859038/modeless-form-that-still-pauses-code-execution/16860597#16860597) or [here](https://stackoverflow.com/questions/26002837/allow-user-to-make-entries-on-worksheet-during-run-time/26006961#26006961) for two different but similar approaches to this sort of problem. – David Zemens May 10 '19 at 14:21
  • Can you try using a do while or any loop to keep looping till active workbook (you will be editing the active workbook to rename the sheet) has a sheet called 'Template'. – David Jones May 10 '19 at 14:57
  • Thanks all. The links provided the answers I was looking for. It's not elegant, but it works. Thanks – gudal May 11 '19 at 17:50
  • I guess I could also have worked with the sheet codename, though i could potentially lead to a object missing situation when the departments start copying sheets, placing the info in the copied sheet and deleting the old. Not sure it would happen, but it wouldns surprise me either. – gudal May 11 '19 at 17:53

0 Answers0