EXCEL 2016 Blocking automation code

Posted on 2016-11-04
Last Modified: 2016-11-14
I have extensive code that creates reports in EXCEL from SQL DATA.

I have a VB.NET program that calls the sheet.

    Public Sub doit()
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook = Nothing 'New Excel.Workbook
        xlWorkBook = xlApp.Workbooks.Open(My.Settings.ROBSUtility)
        xlApp.Visible = True
        xlApp.Run("printAutoRobs", cmbPrinter.Text)
        xlApp.Visible = False

    End Sub

Open in new window

The code 'printAutoRobs' worked fine last week, and still works fine in  EXCEL 2013 and down on other PC's... so far...

On PC with EXCEL 2016, It launches and begins to execute, but fails when the embedded EXCEL VBA attempts to rename sheet. With the VBA code paused I can't manually rename the sheet either. If I stop the VBA the sheet will rename normally.

The workbook executes properly on that PC, if i launch it normallly and trigger event. Only during automation is the unwanted behaviour exibited.

basically the code in excel does this

make report in template sheet.
copy template sheet
rename copied sheet (code crashes on rename- tried not renaming see below)
add new sheet to array of sheet names
loop til done
print array of sheets to printer designated
delete sheets created during reporting (code crashes here as well - if i just let excel name them above)

I dorked with trust and macro and certificate but still no love...

Any thoughts?
Question by:Grizzler
  • 8
  • 3
LVL 33

Expert Comment

ID: 41875038
Can you post the code for 'printAutoRobs'?

PS You mention 'embedded EXCEL VBA', do you just mean the code in the Excel file you are opening or something else?

Author Comment

ID: 41875463
Just the code in the excel file.  I can post the code but it is complex.

 here is an excerpt of error zone
ws.Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = shopView.Item(i) 'code error here 1004
shtColl.Add shopView.Item(i)

Open in new window

ws is the template sheet that has been filled out
shopView is a collection of resource names used to filter reports from sql
shtColl is a collection of sheet names for later use in printing cycle.

 I have debugged the code, when it stops during automation, on the offending machine and all the data indicates it should work. Again the exact code and runs fine, when not called through automation.
LVL 33

Assisted Solution

Norie earned 500 total points
ID: 41875555
It's never (well very rarely) needed use Activate in Excel VBA.

If I was writing that code, without Activate, it would look like this.
ws.Copy after:=Sheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = shopView.Item(i) 'code error here 1004
shtColl.Add shopView.Item(i)

Open in new window

Give that a try and see if it makes any difference.

Another thing I notice in the posted code is the lack of a workbook reference for Worksheets.Count and Sheets.

Now that might not be a problem, especially if you only have one workbook open, but it would be better to add a workbook reference.

Since I don't know if you have an object variable in the code that references the relevant workbook I would suggest using either ThisWorkbook, which is the workbook the code is in, or ws.Parent, which is the workbook the worksheet ws is in.

So the code would look like  this,
ws.Copy after:=Sheets(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Worksheets.Count).Name = shopView.Item(i) 'code error here 1004
shtColl.Add shopView.Item(i)

Open in new window

or this.
ws.Copy after:=Sheets(ws.Parent.Worksheets.Count)
ws.Parent.Sheets(ws.ParentWorksheets.Count).Name = shopView.Item(i) 'code error here 1004
shtColl.Add shopView.Item(i)

Open in new window


Author Comment

ID: 41875715
I dont use a workbook reference currently.
I definitely should for the instances where the sheet is used manually. The automation creates a new excel instance so 'mostly' it is safe. I will try your suggestions on Monday when I have access to the 'offending' machine. I dont have 2016 office on my dev machines. I find it odd that it wont let me change the sheet name manually, while the code is paused at the "Name=" line. It gives me an error saying there are illegal characters, which is not the case. If I stop the code, it will let me manually change the name. The code still will not run in the spawned instance of excel when launched from the manual 'buttons' in the sheet that allow it to be used without automation.

Basically right now, on this one machine:
Launch Excel from automation, Code Doesn't work
Launch Excel manually, Code works.

Author Comment

ID: 41877544
ws.Copy after:=Sheets(ThisWorkbook.Worksheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Worksheets.Count).Name = shopView.Item(i)

Open in new window

I have hidden sheets in my Sheets Collection. It appears EXCEL reorganizes the index, so hidden sheets are at the end of the index. The copied sheet appears to be 'inserted' after the last visible sheet.  
The sheet that gets renamed is one of the invisible ones. My choice is to make them all visible during the processing, or continue using ThisWorkBook.ActiveSheet.
I confirmed behavior with this code:
Sub indexList()
    Dim i As Integer
    For i = 1 To ThisWorkbook.Sheets.Count
        Debug.Print ThisWorkbook.Sheets(i).Name & " " & i
End Sub

Open in new window

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.


Author Comment

ID: 41877874
Tried the updated code with workbook references and removing activate. Same issue.
Code called by automation fails.

If I just let EXCEL name the copies ... (comment out Name=)  it fails on the second iteration of "copy", saying the sheet name already exists.

Author Comment

ID: 41879593
So, I just did a fresh install on a pristine PC. Same behavior with Fresh copy of EXCEL 2016. This rules out some of my suspects... AntiVirus, Corrupt ?? on PC.

Excel 2016 doesn't allow 'marshalled' automation code to make certain alterations to sheets. Or at least in my scenario, it isn't.

Any other suggestions?

My next course of action is to try it in a disconnected fashion
save printer choice to text file
shell the excel instance
autorun  and read printer choice

Hacky... but should yield an isolated environment ....

Accepted Solution

Grizzler earned 0 total points
ID: 41880847
Here is finished working code:

ROBSUtility has full path to Excel file
    Public Sub printRobs()
        Dim path As String = "c:\visual\buff.txt"
        File.WriteAllText(path, cmbPrinter.Text)
    End Sub

Open in new window

EXCEL ThisWorkbook code module
Private Sub Workbook_Open()
    Dim myFile As String
    Dim myPrinter As String

    myFile = "C:\visual\buff.txt"
    'If file exists process it
    If Len(Dir(myFile)) <> 0 Then

        Open myFile For Input As #1
        If Not EOF(1) Then Line Input #1, myPrinter
        Close #1

        Open myFile For Output As #1
        Print #1, ""
        Close #1

        Dim cnt As Integer
        cnt = Workbooks.Count - 1
        'If file wasn't blank run auto print
        If myPrinter <> "" Then
            printAutoRobs (myPrinter)
            'If I am only workbook and auto printing close excel when done, or just close sheet
            For Each w In Workbooks
                If UCase(Left(w.Name, 12)) = "PERSONAL.XLS" Then cnt = cnt - 1

            If cnt = 0 Then
                Application.DisplayAlerts = False
                ThisWorkbook.Close False
            End If

        End If

    End If

End Sub

Open in new window


Author Comment

ID: 41880908
As a side note, I stated the code worked earlier, then quit.
There was a change in my code that happened at the same time. It may or may not be related.
Initially, printAutoRobs had no parameters.

The call from the executable was

Open in new window

I was resetting the default printer from the exe before this call, but it was not reliably making it to Excel consistently.

Problems did arise after this code change:
xlApp.Run("printAutoRobs", cmbPrinter.Text)

Open in new window

I have not tested to confirm whether altering the code with parameter/ no parameter makes a difference. But, for now I suspect it will and will test on my next project if it comes up. This one is resolved for me as the end result works as intended, and is within my threshold of acceptable hackiness.

I add this info as breadcrumbs to anyone else who may encounter similar issues...

Thanks Norie for taking time to comment!
LVL 33

Expert Comment

ID: 41881078
Glad you got it solved.:)

Author Closing Comment

ID: 41886057
I solved the problem in a completely different fashion than suggested. suggestions did not  resolve issue

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now