Open/Close other workbooks with VBA; 1st one works, 2nd one fails

[ker_01]

Using XL2003, although preferably all code would also work on 2007 for when I
hand this off to another user who uses 2007.

I helped a colleague create some workbooks to collect some data (from
employees with both XL2003 and XL2007). To ensure that the users enabled
macros, we set it up so that the only sheet visible when opened is an
instruction sheet that tells how to enable macros; if macros are enabled then
the Workbook_Open event runs the following code:
Sheet1.Visible = xlSheetVisible 'unhide the data entry sheet
Sheet4.Visible = xlSheetHidden 'hide the macro warning/instructions sheet
Sheet1.Activate 'make sure the data entry sheet is active
CreateIndEmplList 'run code to pull employee names based on the userID of
the person opening the file

That worked just fine (for both XL2003 and XL2007 users); then I was asked
to help aggregate the data as well. I had to disable macros in the data
workbooks so the CreateIndEmplList code wouldn't run when opening the
workbooks to collect the data (partly for speed, and partly because the
person who runs the code may not have direct reports which makes that code
error out and would stop the overall data retrieval), so I ended up with:
Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Debug.print fil
Application.Workbooks.Open (fil), False, True
Debug.print "opened successfully"
Application.AutomationSecurity = secAutomation

So now I've got it set up to loop through the workbooks and scoop out the
data... it opens and closes the first data workbook just fine, but dies on
the second one. I don't even get the "opened successfully" debug output. The
workbook does actually open and I can interact with it manually, but the code
is dead.

Both the first file (which works) and the second file (where it dies) are
saved as xlsm format.

I'm stumped- any idea what might be happening? More code details below.

Thank you,
Keith

Retrieval code snippet:

Sub AggregateSPData()
'Searches the selected folders and sub folders for files with the
specified (xls*) extensions.

Dim i As Integer, z As Long, Rw As Long, ii As Long
Dim Dest1 As Integer, Dest2 As Integer ',Dim i As Integer
Dim WS As Worksheet, ws2 As Worksheet
Dim y As Variant
Dim fldr As String, fil As String, FPath As String
Dim LocName As String
Dim FString As String
Dim SummaryWB As Workbook
Dim SummaryWS As Worksheet
Dim Raw_WS As Worksheet
Dim LastRow As Long, FirstRow As Long, RowsOfData As Long
Dim UseData As Boolean
Dim FirstBlankRow As Long
Dim SourceRange As Variant

'grab current location for later reference, for where to paste final data
Set SummaryWB = Application.ActiveWorkbook
Set SummaryWS = Application.ActiveWorkbook.ActiveSheet

<snip>

fldr = PickAFolder 'brings up the folder picker dialogue
FirstBlankRow = 2

'asd is a 1-D array of files returned
asd = ListFiles(fldr, False)

Set WS = Excel.ThisWorkbook.Worksheets(1) 'destination for data
Set ws2 = Excel.ThisWorkbook.Worksheets(2) 'destination for data
Set ws3 = Excel.ThisWorkbook.Worksheets("AllReceivedIDs")
LastIDRow = ws3.Range("A65536").End(xlUp).Row

On Error GoTo 0
For ii = LBound(asd) To UBound(asd)
'Submitter initials, if files have been renamed
ShortID = Left(asd(ii), 3)
'file name
fil = asd(ii)

'open the file and grab the data
Dim secAutomation As MsoAutomationSecurity
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Debug.Print fil
Application.Workbooks.Open (fil), False, True
Debug.Print "opened successfully"
Application.AutomationSecurity = secAutomation
DoEvents

'Identify the submitter through tracking log
Excel.ActiveWorkbook.Worksheets("Lists").Visible = True
Excel.ActiveWorkbook.Worksheets("Lists").Activate
'<snip>
ThisUser = Excel.ActiveWorkbook.ActiveSheet.Range("K" &
EachUser).Value
Next

LastIDRow = LastIDRow + 1
ws3.Range("A" & LastIDRow).Value = FoundUser

Excel.ActiveWorkbook.Worksheets("Succession Planning").Visible =
True
Excel.ActiveWorkbook.Worksheets("Succession Planning").Activate
'Dynamically pull the submitted data
<snip>
'I've tried just the close line by itself, then added the
security lines in case the Workbook_BeforeClose event was being triggered as
the first workbook was closed (in case that might have caused any problems)

secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks(Dir(fil)).Close SaveChanges:=False
Application.AutomationSecurity = secAutomation
DoEvents


Next ii


MsgBox "Data processing on the selected folder has been completed. Please
move these files to a backup directory so that their data is not accidently
pulled a second time", , "Program Complete"

End Sub

 

[Joel]

At the beginning of the code put this piece of test code to try to isolate
the problem

from
fldr = PickAFolder 'brings up the folder picker dialogue

to
fldr = PickAFolder 'brings up the folder picker dialogue
asd = ListFiles(fldr, False)
For ii = LBound(asd) To UBound(asd)
ShortID = Left(asd(ii), 3)
'file name
fil = asd(ii)
msgbox("FileName : " & fldr)
next ii

 

[ker_01]

The code cycles through the directory (confirms the correct directory). When
I change fldr to fil, it does correctly list all 6 test files in that
directory (full path). Redirected to debug.print for easier copy/paste to
this posting:

FileName : J:\SHARE\SP\Test - All\082009 STSP- Baly NR.xlsm
FileName : J:\SHARE\SP\Test - All\082009 STSP- Gort SR.xlsm
FileName : J:\SHARE\SP\Test - All\082009 STSP- Langer SR.xlsm
FileName : J:\SHARE\SP\Test - All\082009 STSP- Rogert WR.xlsm
FileName : J:\SHARE\SP\Test - All\082009 STSP MTS - SR.xlsm
FileName : J:\SHARE\SP\Test - All\STSP COR Sprangs.xlsm
FileName : J:\SHARE\SP\Test - All\STSP COR Weller.xlsm

During a full (attempted) run, it loads and then closes Baly, then opens
Gort and the VBA dies without any subsequent processing. I checked, and I
don't have any OnErrorResumeNext statements in the master workbook, and I
wouldn't expect the data workbooks to trigger any error handling because the
macro security /should/ be off if my code is doing what I think it is. I can
also open the Gort workbook manually (macros disabled) without any problems.

In the first workbook, the user saved the file with the data sheet visible;
in the Gort workbook the macro warning sheet is visible and the data sheet
was re-hidden. I don't think that should matter, and if it did I'd expect the
problem to show up when I'm unhiding and selecting sheets, not as part of
opening the workbook.

I welcome any additional ideas and suggestions!
Thank you,
Keith

 

[ker_01]

I was just going through the data workbooks, and realized that Baly.xlsm
doesn't have any macros, at all. Someone must have saved the file as a
non-macro enabled workbook, then either resaved it as xlsm or maybe just
changed the file extension. So the code is dying the first time it hits a
workbook that actually has code in it. Is there something wrong with
how/where/when I turn security off and then on again? Is there a better way
to open a child workbook and prevent it's macros from running?

Thanks!
Keith

 

[Joel]

Why do you have parethesis in this statement?

Application.Workbooks.Open (fil), False, True

I prefer this

Application.Workbooks.Open filename:=fil, updatelinks:=False, readonly:=True

The readonly should stop any macros from running. I don't use security when
open files but I found this remark in the VBA help which might help

Setting ScreenUpdating to False does not affect alerts and will not affect
security warnings. The DisplayAlerts setting will not apply to security
warnings. For example, if the user sets DisplayAlerts equal to False and
AutomationSecurity to msoAutomationSecurityByUI, while the user is on Medium
security level, then there will be security warnings while the macro is
running. This allows the macro to trap file open errors, while still showing
the security warning if the file open succeeds.

 

[ker_01]

Joel, thank you for your continued assistance.

I changed the .open statement to the longer syntax you suggested (easier to
read).
I also commented out all my security lines, to see if they might be hurting
more than helping. I then put in breakpoints to walk through the code at the
file open and file close statements. Here are my results:

File 1 opens and closes as expected. File 2 opens; then the VBE switches to
show Module 1 in the child workbook, but there is no highlighting (no
indication of an active line). The VBE icons change- instead of stop/pause
being available, only the play icon is available. It is terminating without
any error or warning.

To further test my idea about the macros being a problem, I saved a copy of
File 2 as an .xlsx and renamed so that it would be processed first. It
processed just fine; the old workbook number 1 processed after that ... then
it went to open Gort (the original copy, still with macros) and it died as
before.

So the issue seems to be with 2003 trying to open a 2004 xlsm workbook
without triggering the macros. For reference, the macros in the child
workbooks check the currently logged in userID and change some data tables-
that would mess up the data I'm trying to extract, so I have to pull the data
without allowing macros to run in the child workbooks.

Thank you again for any continued assistance and advice!
Best,
Keith

 

[Joel]

There is a option in VBA to break on all errors

Tools - Options - General - Break on All Errors

You can also put a stop instruction in the workbook that is failing to
isolate any problems with workbook open macros.

 

[ker_01]

That's a good tip- I'm not formally trained, and didn't know about the 'break
on all errors' option.

I turned that on, and also added a line at the top of the workbook_open
event of the child workbook where it dies each time:

Private Sub Workbook_Open()
MsgBox "KEITH TESTING"
<snip>
End Sub

The behavior hasn't changed; it opens the offending child workbook (like
with the other child workbooks, I see the "converting" prompt as it converts
the format into something Excel 2003 can handle). It then stops, still with
no error messages or warnings. It doesn't run the workbook_open VBA of the
child workbook (although I can't tell whether it doesn't run it at all, or if
it just dies before executing that event).

I'm starting to think that maybe it is a 2003/2007 compatibility issue?
Thank you,
Keith

 

[ker_01]

Even though I can't see an error, I got the brilliant idea of putting in an
"on error resume next" above the .open line, and "on error goto 0" a line
down (after a debug.print line). I was thinking that maybe I could get it to
just skip over whatever error it ran into- but no joy - it still stops in the
exact same place.

 

[ker_01]

I decided to try running the code on a 2007 machine (I have limited access to
2007 machines, so it isn't my regular programming/testing platform). When I
ran the code, it did open each file and didn't die on the first xlsm with
code. However, it /did/ execute the workbook_open code in each (I got the
"Keith test" messagebox). So, I uncommented the security code, and now
everything works as expected...it loads all the data, and doesn't execute
child workbook code. Everything works... except that it won't work on a 2003
box.

This is going to be an ongoing project, so I'd still welcome any thoughts on
how to get this running on a 2003 box.

Many thanks,
Keith

 

[Joel]

I tink yo are correct that it is a 2003/2007 compatabilty problems. I don't
work often with 2007 so I don't have any suggestions.