Print Another Sheet with button

English
,
#1

So i have a code that i can print another sheet on my active sheets now, the problem is if i click that button it automatically redirect me to that another sheet to print.

Can i make it run in the background so i dont need to redirect to that sheet for printing.

This is the code

Sub CetakSheetLainNTB
    Dim oDoc As Object
    Dim oSheets As Object
    Dim oSheetTarget As Object
    Dim oController As Object
    
    oDoc = ThisComponent
    oSheets = oDoc.Sheets
    
    ' 1. GANTI "NamaSheetTujuan" dengan nama sheet yang ingin dicetak
    Dim namaSheet As String
    namaSheet = "BUTAB ATM NTB"
    
    If oSheets.hasByName(namaSheet) Then
        oSheetTarget = oSheets.getByName(namaSheet)
        
        ' 2. Atur agar sheet target menjadi yang "terpilih" di latar belakang
        oController = oDoc.getCurrentController()
        oController.select(oSheetTarget)
        oDoc.LockControllers()
        
        ' 3. Konfigurasi opsi cetak: Hanya cetak sheet yang dipilih
        Dim printOpts(0) As New com.sun.star.beans.PropertyValue
        printOpts(0).Name = "PrintSheets"
        printOpts(0).Value = Array(oSheetTarget) ' Hanya sheet ini yang dikirim ke printer
        
        ' 4. Jalankan perintah print langsung
        oDoc.print(printOpts())
        oDoc.UnlockControllers()
    End If
End Sub
#2

There is a line to select this sheet

oController.select(oSheetTarget)

I would try what happens, if I remove that line (or put it into a comment/ REM )

But because of the lockController is assume you’d still have to wait until the macro finishes.

#3

You need to return to the original sheet programmatically.

Sub PrintSpecificSheet()
    Dim oDoc As Object
    Dim oSheets As Object
    Dim oSheet As Object
    Dim oController As Object
    Dim oOldSheet As Object
    Dim sSheetName As String
    Dim printArgs(0) As New com.sun.star.beans.PropertyValue
    
    ' Set the name of the sheet you want to print
    sSheetName = "BUTAB ATM NTB" 
    
    oDoc = ThisComponent
    oSheets = oDoc.getSheets()
    If oSheets.hasByName(sSheetName) Then
      oController = oDoc.getCurrentController()
      oSheet = oSheets.getByName(sSheetName)
     ' Save the current active sheet to restore it later
      oOldSheet = oController.getActiveSheet()
      
      ' Setup print arguments: Wait until the print job is completely sent to the spooler
      printArgs(0).Name = "Wait"
      printArgs(0).Value = True
      
      ' Switch to the target sheet programmatically
      oController.setActiveSheet(oSheet)
    
      ' Print the document synchronously
      oDoc.print(printArgs())
    
      ' Restore the original active sheet
      oController.setActiveSheet(oOldSheet)
    End If  
 End Sub
#4

Esta solução gera um PDF temporário, e imprimi sem sair da Planilha Ativa.

This solution generates a temporary PDF and prints it without leaving the active worksheet.

Sub PrintAnotherSheet
    Dim oDoc As Object
    Dim oArea As Object
    Dim oSheet As Object
    Dim oSFDoc As Object
    Dim oPDF As Object
    Dim oDesktop As Object

    Dim oEnd As Object
    Dim sRange As String
    Dim sPDF As String

    Dim aLoad(0) As New com.sun.star.beans.PropertyValue
    Dim aPrint(2) As New com.sun.star.beans.PropertyValue

    oDoc = ThisComponent

    '------------------------------------------------------
    ' Localiza SAIDAIMPRESSAO
    '------------------------------------------------------
    If Not oDoc.NamedRanges.hasByName("SAIDAIMPRESSAO") Then
        MsgBox "A área SAIDAIMPRESSAO não foi encontrada.", _
               48, "ImpressĂŁo"
        Exit Sub
    End If

    oArea = oDoc.NamedRanges _
        .getByName("SAIDAIMPRESSAO") _
        .getReferredCells()

    oSheet = oArea.getSpreadsheet()
    oEnd = oArea.RangeAddress

    '------------------------------------------------------
    ' Monta o endereço no formato:
    ' Planilha2.A1:H50
    '------------------------------------------------------
    sRange = oSheet.Name & "." & _
             ConvertToLetter(oEnd.StartColumn) & _
             (oEnd.StartRow + 1) & ":" & _
             ConvertToLetter(oEnd.EndColumn) & _
             (oEnd.EndRow + 1)

    '------------------------------------------------------
    ' PDF temporário
    '------------------------------------------------------
    sPDF = Environ("TEMP") & "\SAIDAIMPRESSAO_TEMP.pdf"

    If Dir(sPDF) <> "" Then Kill sPDF

    '------------------------------------------------------
    ' ScriptForge Calc associado ao documento
    '------------------------------------------------------
    GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")

    oSFDoc = CreateScriptService("Calc", oDoc)

    '------------------------------------------------------
    ' Exporta somente SAIDAIMPRESSAO
    '------------------------------------------------------
    If Not oSFDoc.ExportRangeToFile( _
        sRange, _
        sPDF, _
        "pdf", _
        True) Then

        MsgBox "NĂŁo foi possĂ­vel criar o PDF.", _
               48, "ImpressĂŁo"

        oSFDoc.Dispose()
        Exit Sub

    End If

    oSFDoc.Dispose()

    '------------------------------------------------------
    ' Abre o PDF oculto
    '------------------------------------------------------
    oDesktop = createUnoService("com.sun.star.frame.Desktop")

    aLoad(0).Name = "Hidden"
    aLoad(0).Value = True

    oPDF = oDesktop.loadComponentFromURL( _
        ConvertToURL(sPDF), _
        "_blank", _
        0, _
        aLoad())

    If IsNull(oPDF) Or IsEmpty(oPDF) Then

        MsgBox "NĂŁo foi possĂ­vel abrir o PDF.", _
               48, "ImpressĂŁo"

        Kill sPDF
        Exit Sub

    End If

    '------------------------------------------------------
    ' Imprime o PDF
    '------------------------------------------------------
    aPrint(0).Name = "Wait"
    aPrint(0).Value = True

    aPrint(1).Name = "CopyCount"
    aPrint(1).Value = 1

    aPrint(2).Name = "Collate"
    aPrint(2).Value = True

    oPDF.print(aPrint())

    '------------------------------------------------------
    ' Fecha o PDF
    '------------------------------------------------------
    oPDF.close(True)

    '------------------------------------------------------
    ' Exclui o arquivo temporário
    '------------------------------------------------------
    If Dir(sPDF) <> "" Then Kill sPDF

End Sub


Function ConvertToLetter(nCol As Long) As String

    Dim n As Long
    Dim s As String

    n = nCol + 1

    Do While n > 0
        n = n - 1
        s = Chr(65 + (n Mod 26)) & s
        n = Int(n / 26)
    Loop

    ConvertToLetter = s

End Function
#5

:speak_no_evil: :speak_no_evil:

sRange = oDoc.NamedRanges.getByName("SAIDAIMPRESSAO").Content
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.