0
The form below refers to the code under VBA (Excel) presented below.
This code refers to the printing of a spreadsheet on Excel by name "Imprint", as shown in the figure below. By the parameters passed on each button, will be printed all the data that appear in this worksheet, ie, is not the printing of a certain "range of cells".
In the form of VBA the two buttons are named after:
CommandButtonImprimeEmPDF
CommandButtonImprimeEmTXT
And in the code there’s the routine ImprimeOuSalvaEmArquivo intended for printing or saving (generic form), in which case printing on archives shall be tested.
It is verified in the code that for the same "Path" and "filename" informed, as the event "click" of each button, the extension changes in the parameters passed to the printing routine, from PDF for TXT respectively.
When you press each button, the messages below are displayed indicating the location, name and extension of the generated files.
The figure below shows the result in briefcase, where the files were generated correctly as to the place, name and extent.
When opening the file in PDF it is as predicted, but the file in TXT no, that is to say, it does not display the sheet text as expected (in text format).
The code goes below:
Option Explicit
Private Sub CommandButtonImprimeEmPDF_Click()
'Salva impressão como arquivo em PDF
ImprimeOuSalvaEmArquivo "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 by changing the internal names of printers that I have, they differ from the names present in the list below, it was necessary to take these names by means of code by VBA after selecting the printer and using a Msgbox, for example. According to the figure below, files were generated TXT in various formats, but not in the desired format (I did not present these tests here, as they were one for each printer in the list).
A remark: if the print option in file is passed in the respective parameter, the dialog box for selecting the printer is not shown even if this has been requested by its parameter. If you want to test print for files using printer selection this part of the code needs to be adapted.
I know there are other ways to generate files TXT, for example, by means of macro, but I would like to use the routine that I have presented as it presents several possibilities to treat printing, such as the inclusion of titles and modification of margins (here the routine is simplified).
In reference to the instruction used (ActiveWindow.SelectedSheets.PrintOut) nay
found how to properly generate the file TXT not even if it is possible through her.
I wonder if missed to indicate something in this statement or if she doesn’t do what I intend, generate files TXT in order to display the same layout as the spreadsheet data.
