Solved

EXCEL 2016 Blocking automation code

Posted on 2016-11-04
11
31 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

18 Experts available now in Live!

Get 1:1 Help Now