Failed to generate TXT file by routine for printing in VBA / Excel

0

The form below is the code in VBA (Excel) below.

ThiscodereferstoprintinganExcelspreadsheetofname" Printing " as shown in the figure below. By the parameters passed in each button, all the data that appears in this worksheet will be printed, that is, it is not the impression of a certain "range of cells".

IntheVBAformthetwobuttonsarenamed:

CommandButtonImprimeEmPDFCommandButtonImprimeEmTXT

AndinthecodethereistheroutineImprimeOuSalvaEmArquivointendedforprintingorsaving(inagenericway),inwhichcasetheprintingonfilesistested.

Itischeckedinthecodethatforthesame" Path " and " filename " reported, depending on the click event of each button, the extension changes PDF for TXT respectively.

When each button is pressed, the following messages are displayed, informing the location, name and extension of the generated files.

Thefigurebelowshowstheresultinfolder,wherethefilesweregeneratedcorrectlyforlocation,nameandextension.

WhenyouopenthefileinPDFitisasexpectedbutthefileinTXTdoesnot,ieitdoesnotdisplaythetextoftheworksheetasexpected(intextformat).

Thecodefollowsbelow:

OptionExplicitPrivateSubCommandButtonImprimeEmPDF_Click()'SalvaimpressãocomoarquivoemPDFImprimeOuSalvaEmArquivo"Impressão", _
                             "", _
                             True, _
                             "F:\Teste\TesteDeImpressao1.PDF", _
                             False, _
                             "", _
                             1, _
                             True

     'Veja o significado das opções na rotina de impressão e salvamento

     End Sub


     Private Sub CommandButtonImprimeEmTXT_Click()

      'Salva impressão como arquivo em TXT
      ImprimeOuSalvaEmArquivo "Impressão", _
                              "", _
                              True, _
                              "F:\Teste\TesteDeImpressao1.TXT", _
                              False, _
                              "", _
                              1, _
                              True

     'Veja o significado das opções na rotina de impressão e salvamento

    End Sub


    Private Sub ImprimeOuSalvaEmArquivo( _
                ByVal NomeDaPlanilha As String, _
       Optional ByVal FaixaParaImprimir As String = "", _
       Optional ByVal ImprimirEmArquivo As Boolean = False, _
       Optional ByVal CaminhoNomeExtensaoDoArquivo As String = "", _
       Optional ByVal SelecionarImpressora As Boolean = False, _
       Optional ByVal NomeInternoDaImpressora As String = "", _
       Optional ByVal NumeroDeCopias As String = 1, _
       Optional ByVal MensagemAoFinalDaImpressao = False)

     Dim Planilha As Object

     'O objeto Planilha assume a planilha selecionada
     Set Planilha = Sheets(NomeDaPlanilha) 'Planilha anteriormente selecionada

     'Ativa a planilha a salvar/imprimir
     Planilha.Activate

     'Se optou por selecionar a impressora
     'e não solicitou salvar em arquivo
     If SelecionarImpressora _
        And Not ImprimirEmArquivo Then

         Application.Dialogs(xlDialogPrinterSetup).Show

     End If

     'Se o nome da impressora não foi informado anteriormente
     If NomeInternoDaImpressora = "" Then

         'Pega o nome da impressora ativa
         NomeInternoDaImpressora = ActivePrinter

     End If

     'Faixa a imprimir ou toda a planilha se vier com ""
     Planilha.PageSetup.PrintArea = FaixaParaImprimir

     'Imprime ou salva em arquivo
     ActiveWindow.SelectedSheets.PrintOut _
         Copies:=NumeroDeCopias, _
         ActivePrinter:=NomeInternoDaImpressora, _
         PrintToFile:=ImprimirEmArquivo, _
         PrToFileName:=CaminhoNomeExtensaoDoArquivo

     Beep

     'Se solicitou mensagem de local e nome do arquivo salvo/impresso
     If MensagemAoFinalDaImpressao Then

         MsgBox "Local e Nome do Arquivo Salvo/Impresso: " _
                & Chr(13) & Chr(13) & _
                CaminhoNomeExtensaoDoArquivo

     End If

 End Sub  

I tested changing the internal names of printers that I have, they differ from the names in the list below, I had to get those names through code by VBA after selecting the printer and using a MsgBox , for example. As the figure below, TXT files were generated in various formats, less than intended (I did not present those tests here, as they were one for each printer in the list).

A note : If the option to print to file is passed in its parameter, the dialog box for selecting the printer is not displayed, even if this has been requested by its parameter. If you want to test printing to files using printer selection this part of the code needs to be adapted.

I know there are other ways to generate TXT files, for example through macro , but I would like to use the routine I submitted, since it has several possibilities for treat the impression, such as the inclusion of titles and modification of the margins (here the routine is simplified).

Regarding the instruction used ( ActiveWindow.SelectedSheets.PrintOut ) does not I found how to correctly generate the TXT file or even if it is possible through it.

I would like to know if was missing something in this statement or if it does not do what I want , generate TXT files to display the same layout of the spreadsheet data.

    
asked by anonymous 26.06.2016 / 03:24

1 answer

0

I found the solution, thought to generate a printer that only printed in text file , I searched and found this link:

link

Although it is an example in the previous version of Windows, I followed the steps and adapted to my case as I present the sequence that follows.

  • Add a printer :
  • Printer Local
  • Port LPT1
  • Generic and " Generic / Text Only "

  • Printer Name . I left the " Generic / Text Only " default:
  • Sharing, I chose not to share
  • Finish ( unchecked "set as default" )
  • Theprinterbelowwasgenerated:

    I'vecreateda"Print" button to test again, and I've adapted the print parameters to request the printer before printing:

    Private Sub CommandButtonImprimir_Click()
    
     ImprimeOuSalvaEmArquivo "Impressão", _
                             "", _
                             False, _
                             "F:\Teste\TesteDeImpressao2.TXT", _
                             True, _
                             "", _
                             1, _
                             True
    
    End Sub
    

    Notice that I have changed not only the file name, but I have enabled the " select printer " parameter.

    The printer for text files came up:

    Andtheresultwasthis:

    Theimpressioncameoutasdesired,butInoticedonecharacterattheendoftheprint.I'mgoingtoaskanotherquestionaboutthis,becauseIhavenoideaifyoucannotdothischaracterwhengeneratingthetextfile.Thelinkforthisnewissueis:

    link

        
    26.06.2016 / 15:22