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
AndinthecodethereistheroutineImprimeOuSalvaEmArquivo
intendedforprintingorsaving(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.