VBA Excel Macro Opens a workbook but everything freezes because that file tries to run its own macro

Question

So, I have a macro that is supposed to go to a folder and cycle through each workbook in the folder and pull certain data. The folders of data have files from different years, and the newer files work just fine. However when I open some of the older files, I have a problem. The older files were saved as .xls files, but when you open them the only worksheet that shows says "EnableMacros", with a warning that the user must change their security settings to enable macros. Since my options and trust center settings are set to allow macros to run without restriction, when I open the workbook normally the EnableMacros tab shows for a second then is replaced by the normal tabs with the data in them. However, when I have my macro open up the files the program simply stops and the opened workbook just shows the EnableMacros tab and wont update to show the worksheets with the data. I don't know how to work around this. And this is all complicated by the fact that I can't change anything in the workbooks I am opening since they are protected. Help?

I am using the macro provided by The Spreadsheet Guru to go through the files in the folder, with my own modifications to pull the data that I want:

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'transfer values with range
Dim rngcopySA As Range

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)

    'Ensure Workbook has opened before moving on to next line of code
      DoEvents

Here is where things break, because the correct worksheet doesn't show up, only the "EnableMacros" worksheet. The error that comes up says "Run-time error '1004': Select method of Worksheet class failed".

    'Copy Range with Membership Data to this file
     wb.Worksheets("Service Areas").Select

      'transfer values with range
      wb.Worksheets("Service Areas").Range("A13:E13").Select
      Set rngcopySA = wb.Worksheets("Service Areas").Range(Selection, Selection.End(xlDown))

      ThisWorkbook.Activate
      Sheets("Old SA Files").Select
      Range("A" & Rows.count).End(xlUp).Offset(1).Resize(rngcopySA.Rows.count, rngcopySA.Columns.count).Cells.Value = rngcopySA.Cells.Value

'Save and Close Workbook
    wb.Close SaveChanges:=True

'Ensure Workbook has closed before moving on to next line of code
  DoEvents

 'Get next file name
   myFile = Dir
 Loop

ResetSettings:
 'Reset Macro Optimization Settings
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

End Sub

As I said before, this works just fine for the newer files, it is only the older ones that have that "EnableMacros" tab that have the problem. I have also tried going through each of the old .xls files in a folder and hitting the Convert button to get out of compatibility mode, and so changed all the files to .xlsm (and changed the folder picker code to look for .xlsm files) but that had the exact same problem.

SeanC · Accepted Answer · 2017-06-02T11:38:31.650

2

The person that has set up the spreadsheet has used xlVeryHidden to stop people from unhiding the sheets without using the macro

To select one of the hidden sheets, you have to make it visible first, and that is simply

Sheets("Service Areas").Visible = True

you should then be able to select the sheet.

Note that even if the sheet is hidden, you can still select the data and copy it, so you could change the code causing an issue to this:

'Copy Range with Membership Data to this file
  'transfer values with range
  Set rngcopySA = wb.Worksheets("Service Areas").Range("A13:E13", wb.Worksheets("Service Areas").Range("A13:E13").End(xlDown))

  ThisWorkbook.Sheets("Old SA Files").Range("A" & Rows.count).End(xlUp).Offset(1).Resize(rngcopySA.Rows.count, rngcopySA.Columns.count).Cells.Value = rngcopySA.Cells.Value

note the complete lack of Select which avoids screen flicker and other issues. See this question for more details on avoiding select.

edited Jun 02 '17 at 11:38

answered Jun 01 '17 at 19:12

SeanC

15,695
5
45
66

Ok, so the `Sheets("Service Areas").Visible=True` worked, and now when everything stops that sheet is visible. Hooray! The new error message says "Application-defined or object-defined error." Everything still works for the new files. – Lui Jun 01 '17 at 20:19
That error message occurs at the line `Set rngcopySA = wb.Worksheets("Service Areas").Range("A13:E13", Range("A13:E13").End(xlDown))`. – Lui Jun 01 '17 at 20:28
I added the sheet reference to the range - as we are not making the specific range active, we have to make sure that every range is properly qualified – SeanC Jun 02 '17 at 11:39
Thank you! I have some other problems with my code now, but I think this part is solved! – Lui Jun 02 '17 at 20:36

VBA Excel Macro Opens a workbook but everything freezes because that file tries to run its own macro

1 Answers1