Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


EXCEL 2016 Blocking automation code

Posted on 2016-11-04
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
LVL 35

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 35

Assisted Solution

Norie earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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


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 35

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

636 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