文章/答案/技术大牛

发布

社区首页 >问答首页 >Excel VBA，使用"Microsoft Print to PDF“将”安全“PDF打印为新的无保护PDF。

问Excel VBA，使用"Microsoft Print to PDF“将”安全“PDF打印为新的无保护PDF。
EN

Stack Overflow用户

提问于 2022-08-26 13:47:24

回答 1查看 50关注 0票数 1

我已经创建了一个宏，可以从PDF导入表，每天必须有60个PDF，但是现在我发现那些PDF是受保护的，所以VBA无法访问其中的信息。除非我把它打印成新的PDF格式。

我已经能够打开打印提示，但由于信息量大，我需要它更“自动”。我不想选择保存它的路径，甚至它的名字。

我不知道下一步该做什么--这是我用来打开和打印PDF的代码

   Option Explicit

   Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

   Private Const SW_HIDE As Long = 0&
  
    Public Sub Print_PDFs()

   Dim caminho As String

   caminho = "C:\Users\93250121\Desktop\Tadeu\100722081309300_20563822_47774_20220824164855979_8.pdf"
               
               ShellExecute_Print caminho
  
   End Sub


   Public Sub ShellExecute_Print(file As String, Optional printerName As String)
   If printerName = "" Then
       ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
   Else
       ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
   End If
   End Sub

excel

vba

pdf

printing

回答 1

Stack Overflow用户

发布于 2022-08-26 23:11:26

我已经能够创建下面的代码来帮助解决这个问题。所以第一个问题不再是必要的了。

Sub ler_tabela_via_query()

Dim Local_Arq As String
Dim Pasta As String
Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As file

Set p_vba = ActiveWorkbook.Worksheets("Planilha2")
Set dados = ActiveWorkbook.Worksheets("Planilha1")


'Inerir onde serão armazenadas os PDF's
Pasta = "C:\Users\93250121\Desktop\Tadeu"

'A macro vai ler todos os arquivos que estiverem na pasta - então é essencial que na pasta só tenham arquivo em PDF que vocxê quer transferir
Set fo = fso.GetFolder(Pasta)

For Each f In fo.Files

'puxa o caminho inteiro do arquivo inclusivi o ".pdf"
Local_Arq = f.Path


'aciona a query puxando as tabeles 2 e 3 que contem respectivamente nome da empresa e informações da operação - Codigo está em M
  ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Fonte = Pdf.Tables(File.Contents(""" & Local_Arq & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table002 = Fonte{[Id=""Table002""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Tipo Alterado"" = Table.TransformColumnTypes(Table002,{{""Column1"", type text}, {""Column2"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Tipo Alterado"""
    ActiveWorkbook.Queries.Add Name:="Table003 (Page 1)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Fonte = Pdf.Tables(File.Contents(""" & Local_Arq & """), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & "    Table003 = Fonte{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Cabeçalhos Promovidos"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Tipo Alterado"" = Table.TransformColumnTypes(#""Cabeçalhos Promo" & _
        "vidos"",{{""Característica"", type text}, {""Custódia"", type text}, {""Liquidação"", type text}, {""Datada#(lf)Contratação"", type text}, {""Vencimento"", type text}, {""Prazo (dias)"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Tipo Alterado"""
    
    p_vba.Activate
   With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""" _
        , Destination:=Range("$A$2")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table002 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .PreserveColumnInfo = True
        .Refresh
    End With
    
     p_vba.Activate
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table003 (Page 1)"";Extended Properties=""""" _
        , Destination:=Range("$A$7")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table003 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .PreserveColumnInfo = True
        .Refresh
    End With
  
  ' sleep é uma chamada tipo wait mas melhor o codigo está definido lá em cima
 Sleep (100)
  
  ' Essa parte está no modulo 3 ele vai apagar todas as conexões do excel para poder puxar o proximo extrato
  Call DeleteAllQueriesAndConnections
  
  ' essa parte é sussa, ele só vai alinhar as informações e colar na aba padronizada
 p_vba.Activate
    Cells(1, 1) = Range("A3").Value
    Range("B1", "G1") = Range("A8", "F8").Value
    Range("H1", "L1") = Range("A10", "E10").Value
    Range("M1", "Q1") = Range("A12", "E12").Value
  
  p_vba.Columns("J:J").Select
    Selection.Delete Shift:=xlToLeft
    
 Range("A1").Select
 Range(Selection, Selection.End(xlToRight)).Copy
 
 dados.Activate
 Range("A100000").Select
 Selection.End(xlUp).Offset(1, 0).Select
 
 ActiveCell.PasteSpecial xlPasteValues
    
 p_vba.Columns("A:Z").Delete Shift:=xlToLeft
   
  
Next
  
End Sub

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/73502034

复制

相似问题

问Excel VBA，使用"Microsoft Print to PDF“将”安全“PDF打印为新的无保护PDF。
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel VBA，使用"Microsoft Print to PDF“将”安全“PDF打印为新的无保护PDF。EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel VBA，使用"Microsoft Print to PDF“将”安全“PDF打印为新的无保护PDF。
EN