locked
Running Excel VBA from a VB.net Application to change printer RRS feed

  • Question

  • Hello everyone,

    A while ago I made a small application which runs a set of Excel Macros to generate some reports. So far the application ran just fine for about a year, however a few new computers were bought and some functions stopped working on these new computers. All the macros run just fine when they're run from within Excel and all the macros are executed fine when they're called by the VB.NET application.

    However, there is one macro that changes the current printer on Excel before printing the reports and for some reason it is no longer changing the current printer when called from vb.net but changes it just fine when ran from the VBA editor. 

    The macro that sets the printer looks like this

    Function setPrinter(print As String) As Boolean
        Dim actprinter$
        Dim setPrinter2 As Boolean
        setPrinter2 = True
        Range("Printer") = print
        For i = 0 To 20
            On Error Resume Next
            If (i <= 10) Then
                actprinter = print & " on Ne" & Format$(i, "00") & ":"
            Else
                actprinter = print & " en Ne" & Format$(i - 11, "00") & ":"
            End If
            Application.ActivePrinter = actprinter
            If Err.Number = 0 Then Exit For
        Next i
        If Application.ActivePrinter <> actprinter Then setPrinter2 = False
        setPrinter = setPrinter2
    End Function


    And @ Vb.NET

    Public xlApp As New ObjectxlApp = CreateObject("Excel.Application")
    If Not CBool(xlApp.Run("setPrinter", Printers.SelectedItem)) Then
        MsgBox("Couldn't set Printer.", vbCritical, "Reports Cancelled")
        Exit Sub
    End If
    I tried running with Admin priviledges just in case, but no luck. Not sure if there might be some sort of library tha I am missing, so any suggestions are more than welcome. 

    • Moved by Cindy Meister MVP Monday, November 9, 2015 9:02 PM not using vsto technology
    Monday, November 2, 2015 7:21 PM

Answers

  • >>>However, there is one macro that changes the current printer on Excel before printing the reports and for some reason it is no longer changing the current printer when called from vb.net but changes it just fine when ran from the VBA editor.

    According to youre description and sample codes that you have posted, I find your "setPrinter" Function that there are some problems.  When you set Application.ActivatePrinter through setPrinter" Function, It will always return false.

    So I suggest that you could make sure whether the print is same as the printer.


    Friday, November 6, 2015 1:46 AM

All replies

  • >>>However, there is one macro that changes the current printer on Excel before printing the reports and for some reason it is no longer changing the current printer when called from vb.net but changes it just fine when ran from the VBA editor.

    According to youre description and sample codes that you have posted, I find your "setPrinter" Function that there are some problems.  When you set Application.ActivatePrinter through setPrinter" Function, It will always return false.

    So I suggest that you could make sure whether the print is same as the printer.


    Friday, November 6, 2015 1:46 AM
  • Hello David,

    Thanks for your reply, but I am not sure what do you mean by this:

    >>> When you set Application.ActivatePrinter through setPrinter" Function, It will always return false.

    I mean, when I run the Macro from VBA it does return true and the printer is successfully modified, however, the problem comes from running it from the VB.net app and using in both cases the same argument for "print"

    Can you please elaborate a little bit on this?

    • Edited by Xgito Wednesday, November 18, 2015 3:19 PM
    Wednesday, November 18, 2015 3:18 PM