Solved

EXCEL 2016 Blocking automation code

Posted on 2016-11-04
11
45 Views
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
        xlWorkBook.Close(False)
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    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?
0
Comment
Question by:Grizzler
  • 8
  • 3
11 Comments
 
LVL 33

Expert Comment

by:Norie
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?
0
 

Author Comment

by:Grizzler
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.Activate
ws.Copy after:=Sheets(Worksheets.Count)
ActiveSheet.Name = shopView.Item(i) 'code error here 1004
shtColl.Add shopView.Item(i)
ws.Activate

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.
0
 
LVL 33

Assisted Solution

by:Norie
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

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Grizzler
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.
0
 

Author Comment

by:Grizzler
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
    Next
End Sub

Open in new window

0
 

Author Comment

by:Grizzler
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.
0
 

Author Comment

by:Grizzler
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
IE
save printer choice to text file
shell the excel instance
autorun  and read printer choice

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

Accepted Solution

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

.NET EXE
ROBSUtility has full path to Excel file
    Public Sub printRobs()
        Dim path As String = "c:\visual\buff.txt"
        File.WriteAllText(path, cmbPrinter.Text)
        Process.Start(My.Settings.ROBSUtility)
    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
            Next

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

        End If

    End If

End Sub

Open in new window

0
 

Author Comment

by:Grizzler
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
xlApp.Run("printAutoRobs")

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!
0
 
LVL 33

Expert Comment

by:Norie
ID: 41881078
Glad you got it solved.:)
0
 

Author Closing Comment

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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 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