Macro ending without hitting "exit sub" "end sub" or generating an error. 1

[PureSlate]

Hi all,

I have an interesting issue I've run into. I have a macro that is used to import data from a selected file into a specific location. This macro worked fine in Excel 2007, but has an odd glitch where it will just end when run in 2010.

I've tried putting break points on each line that would cause the macro to end, but it never hits those, and never generates an error. (Yes, I double checked that the screen can update and errors can be displayed while testing.)

I think I've narrowed down the issue, but I'm not quite sure how to fix it. This piece of code: (Don't judge)

If WIP <> "False" Then
    Set wipwb = Workbooks.Open(Filename:=WIP)
    wipwb.Worksheets("ETD WIP").Columns("A:U").Copy (wb.Worksheets("ETD WIP DATA").Columns("A:U"))
    wipwb.Close
Else
    Exit Sub
End If

Specifically, the code seems to just "drop" when the "Set wipwb = Workbooks.Open(Filename:=WIP)" line is run. Now, the file the user will select (and the only one that will work) is an .xls file that has macros in it. (Again, don't judge, I have no control over that.)
Now for the fun part. When I step through the code, it works perfectly. Or at least, until I disabled the macro on the target workbook when opening it. I was left with no macro running, looking at the new workbook, but the next line (wipwb.Worksheets("ETD WIP").Columns("A:U").Copy (wb.Worksheets("ETD WIP DATA").Columns("A:U"))) DID run.

Does anyone have insight into this?

 

[SkipVought]

Hi,

What's the macro security setting on your PC?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If the WIP workbook contains macro and you need to block it, change Application.AutomationSecurity (to ForceDisable before opening workbook and Low - default after).

combo

 

[PureSlate]

Hey Skip,

The trust settings pushed out are "disable all macros with notification" and "trust access to VBA project object model" is not checked. Combo - the settings you recommended, when implemented (using the msdn example) didn't change the outcome. I did stumble on a way to make the macro run successfully everytime, but, unfortunately, I have to comment out a line. This is handy though, since it means the problem is occurring on or because of this specific line. When stepping through the code even, the copy line seems to be generating an issue. (The wipwb workbook closes though, indicating it runs past the troubled line.) The following code runs, assuming I transfer the data using the commented out line prior to running the macro.

If WIP <> "False" Then
    secAutomation = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Set wipwb = Workbooks.Open(Filename:=WIP)
'   wipwb.Worksheets("ETD WIP").Columns("A:U").Copy (wb.Worksheets("ETD WIP DATA").Columns("A:U"))
    wipwb.Close
    Application.AutomationSecurity = secAutomation
Else
    Exit Sub
End If

 

[SkipVought]

Try this

 wipwb.Worksheets("ETD WIP").Columns("A:U").Copy (wb.Worksheets("ETD WIP DATA").[highlight]Range("A1")[/highlight])

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PureSlate]

No change.

I really suspect this issue is related to the macro within the workbook (system generated workbook, can't change that.)

Is there some easy way to strip out the macro before copying the data? (preferably without having to use .close.

 

[SkipVought]

Is there some easy way to strip out the macro before copying the data?

Copy the data but SaveAs as Excel Workbook (*.xlsx) rather than a Macro-Enabled Workbook, CLOSE that workbook and then OPEN the .xlsx version. You will have no macros.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Rather, do the SaveAs, Close, Open THEN COPY.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PureSlate]

Skip! You are my hero! This works perfectly. It's kinda a workaround-esque solution, and I would love to *not* have to do it, but it works!

Section of code in the end for anyone interested:

Set wb = ThisWorkbook
WIP = Application.GetOpenFilename
If WIP <> "False" Then
    secAutomation = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Set wipwb = Workbooks.Open(Filename:=WIP)
    If Right(WIP, 3) = "xls" Then
        WIP = Replace(WIP, "xls", "xlsx")
        wipwb.SaveAs Filename:=WIP, FileFormat:=xlOpenXMLWorkbook
        wipwb.Close
        Renamed = True
    End If
    Set wipwb = Workbooks.Open(Filename:=WIP)
    wipwb.Worksheets("ETD WIP").Columns("A:U").Copy (wb.Worksheets("ETD WIP DATA").Range("A1"))
    wipwb.Close
    Application.AutomationSecurity = secAutomation
    If Renamed = True Then Kill (WIP)
Else
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub
End If