Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » Excel » Obtener datos de un archivo PDF (MACRO)

Excel - Obtener datos de un archivo PDF (MACRO)

Volver
Nuevo Tema
<<>>
 
Vista:
sin imagen de perfil
Val: 37
Ha disminuido su posición en 6 puestos en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por carlos (36 intervenciones) el 10/08/2022 17:16:36
Un saludo,

Intento hacer una macro que extraiga datos de un archivo pdf (el que seleccione el usuario) y pegue las tablas en una hoja de Excel, mi problema es que necesito que la macro trabaje con el archivo que selecciono el usuario, en el momento el codigo lo tengo montado con una ruta especifica (C:\VARIOS\expertise.pdf").

Mil gracias por la ayuda

Bendiciones



Private Sub btnexaminer_Click()
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = Application.DefaultFilePath & " \ "
.Title = " Ouvrir fichier PDF"
.Filters.Clear
.Filters.Add "PDF files", "*.PDF"
.InitialView = msoFileDialogViewDetails
.Show
If .SelectedItems.count = 0 Then
Else
Me.txtnomfichierpdf.Value = .SelectedItems(1)
End If
End With

With Me
If .txtnomfichierpdf.Value <> "" Then
.btntelechargerpdf.Enabled = True
Else
.btntelechargerpdf.Enabled = False
End If
End With

End Sub

Private Sub btntelechargerpdf_Click()

ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table001 = Source{[Id=""Table001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Table001,{{""Column1"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type modifié"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""" _
, Destination:=Range("$V$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table001__Page_1"
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table002 = Source{[Id=""Table002""]}[Data]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Table002, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = T" & _
"able.TransformColumnTypes(#""En-têtes promus"",{{""Localisation"", type text}, {""soumis"", Int64.Type}, {""Column3"", type text}, {""p/r au prix DSPR"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""p/r estimation"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type modifié"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""" _
, Destination:=Range("$V$10")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table002 (Page 1)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table002__Page_1"
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:="Table003 (Page 1)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Table003 = Source{[Id=""Table003""]}[Data]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = T" & _
"able.TransformColumnTypes(#""En-têtes promus"",{{""code d'ouvrage"", type text}, {""Désignation de l'ouvrage"", type text}, {""Écart"", Int64.Type}, {""Column4"", type text}, {""% de l'écart"", type text}, {""Appréciation"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Type modifié"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table003 (Page 1)"";Extended Properties=""""" _
, Destination:=Range("$V$20")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table003 (Page 1)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table003__Page_1"
.Refresh BackgroundQuery:=False
End With
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveWorkbook.Queries.Add Name:="Page001", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & Chr(13) & "" & Chr(10) & " Page1 = Source{[Id=""Page001""]}[Data]," & Chr(13) & "" & Chr(10) & " #""En-têtes promus"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.Tr" & _
"ansformColumnTypes(#""En-têtes promus"",{{""Column1"", type text}, {""[image]"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", Int64.Type}, {""AUTORISATION DU SOUS-MINISTRE ADJOINT(SMECI)"", type text}, {""Column11"", Percentage.Typ" & _
"e}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", Int64.Type}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", Percentage.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "" & _
" #""Type modifié"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Page001;Extended Properties=""""" _
, Destination:=Range("$V$40")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Page001]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Page001"
.Refresh BackgroundQuery:=False
End With
Range("F4:H4").Select
End Sub


Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Me.btntelechargerpdf.Enabled = False
Me.txtnomfichierpdf.Enabled = False
End Sub
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por Antoni Masana (2478 intervenciones) el 11/08/2022 16:56:10
El código Original.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
Option Explicit
 
Private Sub btnexaminer_Click()
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = Application.DefaultFilePath & " \ "
        .Title = " Ouvrir fichier PDF"
        .Filters.Clear
        .Filters.Add "PDF files", "*.PDF"
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.Count <> 0 Then
           MsgBox .SelectedItems(1)
           Me.txtnomfichierpdf.Value = .SelectedItems(1)
        End If
    End With
 
    With Me
        If .txtnomfichierpdf.Value <> "" Then
            .btntelechargerpdf.Enabled = True
        Else
            .btntelechargerpdf.Enabled = False
        End If
    End With
End Sub
 
Private Sub btntelechargerpdf_Click()
    ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & _
                                                vbCrLf & " Table001 = Source{[Id=""Table001""]}[Data]," & _
                                                vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(Table001,{{""Column1"", type text}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table001__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
 
    ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & _
                                                 vbCrLf & " Table002 = Source{[Id=""Table002""]}[Data]," & _
                                                 vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Table002, [PromoteAllScalars=true])," & _
                                                 vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Localisation"", type text}, {""soumis"", Int64.Type}, {""Column3"", type text}, {""p/r au prix DSPR"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""p/r estimation"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & _
                                                 vbCrLf & "in" & _
                                                 vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$10")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table002 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table002__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
 
    ActiveWorkbook.Queries.Add Name:="Table003 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & _
                                                vbCrLf & " Table003 = Source{[Id=""Table003""]}[Data]," & _
                                                vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & _
                                                vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""code d'ouvrage"", type text}, {""Désignation de l'ouvrage"", type text}, {""Écart"", Int64.Type}, {""Column4"", type text}, {""% de l'écart"", type text}, {""Appréciation"", type text}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table003 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$20")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table003 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table003__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="Page001", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(""C:\VARIOS\expertise.pdf""), [Implementation=""1.3""])," & _
                                                vbCrLf & " Page1 = Source{[Id=""Page001""]}[Data]," & _
                                                vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & _
                                                vbCrLf & " #""Type modifié"" = Table.Tr" & "ansformColumnTypes(#""En-têtes promus"",{{""Column1"", type text}, {""[image]"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", Int64.Type}, {""AUTORISATION DU SOUS-MINISTRE ADJOINT(SMECI)"", type text}, {""Column11"", Percentage.Typ" & "e}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", Int64.Type}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", Percentage.Type}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Page001;Extended Properties=""""", _
                                     Destination:=Range("$V$40")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Page001]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Page001"
        .Refresh BackgroundQuery:=False
    End With
 
    Range("F4:H4").Select
End Sub
 
Private Sub UserForm_Initialize()
    Me.btntelechargerpdf.Enabled = False
    Me.txtnomfichierpdf.Enabled = False
End Sub

El código modificado.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
Option Explicit
 
Private Sub btnexaminer_Click()
    With Application.FileDialog(msoFileDialogOpen)
        .InitialFileName = Application.DefaultFilePath & " \ "
        .Title = " Ouvrir fichier PDF"
        .Filters.Clear
        .Filters.Add "PDF files", "*.PDF"
        .InitialView = msoFileDialogViewDetails
        .Show
        If .SelectedItems.Count <> 0 Then
           MsgBox .SelectedItems(1)
           Me.txtnomfichierpdf.Value = .SelectedItems(1)
        End If
    End With
 
    With Me
        If .txtnomfichierpdf.Value <> "" Then
            .btntelechargerpdf.Enabled = True
        Else
            .btntelechargerpdf.Enabled = False
        End If
    End With
End Sub
 
Private Sub btntelechargerpdf_Click()
    Dim Fichero As String
 
    Fichero = Me.txtnomfichierpdf.Value
 
    ActiveWorkbook.Queries.Add Name:="Table001 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(" & Fichero & "), [Implementation=""1.3""])," & _
                                                vbCrLf & " Table001 = Source{[Id=""Table001""]}[Data]," & _
                                                vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(Table001,{{""Column1"", type text}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table001 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table001 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table001__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
 
    ActiveWorkbook.Queries.Add Name:="Table002 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(" & Fichero & "), [Implementation=""1.3""])," & _
                                                 vbCrLf & " Table002 = Source{[Id=""Table002""]}[Data]," & _
                                                 vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Table002, [PromoteAllScalars=true])," & _
                                                 vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""Localisation"", type text}, {""soumis"", Int64.Type}, {""Column3"", type text}, {""p/r au prix DSPR"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""p/r estimation"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & _
                                                 vbCrLf & "in" & _
                                                 vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table002 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$10")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table002 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table002__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
 
    ActiveWorkbook.Queries.Add Name:="Table003 (Page 1)", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(" & Fichero & "), [Implementation=""1.3""])," & _
                                                vbCrLf & " Table003 = Source{[Id=""Table003""]}[Data]," & _
                                                vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Table003, [PromoteAllScalars=true])," & _
                                                vbCrLf & " #""Type modifié"" = Table.TransformColumnTypes(#""En-têtes promus"",{{""code d'ouvrage"", type text}, {""Désignation de l'ouvrage"", type text}, {""Écart"", Int64.Type}, {""Column4"", type text}, {""% de l'écart"", type text}, {""Appréciation"", type text}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table003 (Page 1)"";Extended Properties=""""", _
                                     Destination:=Range("$V$20")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table003 (Page 1)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table003__Page_1"
        .Refresh BackgroundQuery:=False
    End With
 
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.Queries.Add Name:="Page001", _
                               Formula:="let" & vbCrLf & " Source = Pdf.Tables(File.Contents(" & Fichero & "), [Implementation=""1.3""])," & _
                                                vbCrLf & " Page1 = Source{[Id=""Page001""]}[Data]," & _
                                                vbCrLf & " #""En-têtes promus"" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true])," & _
                                                vbCrLf & " #""Type modifié"" = Table.Tr" & "ansformColumnTypes(#""En-têtes promus"",{{""Column1"", type text}, {""[image]"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", Int64.Type}, {""AUTORISATION DU SOUS-MINISTRE ADJOINT(SMECI)"", type text}, {""Column11"", Percentage.Typ" & "e}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type text}, {""Column20"", Int64.Type}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", Percentage.Type}})" & _
                                                vbCrLf & "in" & _
                                                vbCrLf & " #""Type modifié"""
 
    With ActiveSheet.ListObjects.Add(SourceType:=0, _
                                     Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Page001;Extended Properties=""""", _
                                     Destination:=Range("$V$40")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Page001]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Page001"
        .Refresh BackgroundQuery:=False
    End With
 
    Range("F4:H4").Select
End Sub
 
Private Sub UserForm_Initialize()
    Me.btntelechargerpdf.Enabled = False
    Me.txtnomfichierpdf.Enabled = False
End Sub

Espero te funcione bien.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 37
Ha disminuido su posición en 6 puestos en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por carlos (36 intervenciones) el 11/08/2022 23:43:18
Un saludo Antoni,

Muchas gracias por la valiosa respuesta.

Cuando ejecuto la macro me sale un mensaje diciendo que no reconoce la palabra source.

Aqui le envio el archivo de prueba.

Bendiciones
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por Antoni Masana (2478 intervenciones) el 12/08/2022 13:21:26
El fichero esta vació.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Antoni Masana
Val: 4.908
Oro
Ha mantenido su posición en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por Antoni Masana (2478 intervenciones) el 22/08/2022 01:42:48
¿Y el PDF?

Sin el PDF no puedo probarlo.

Saludos.
\\//_
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 37
Ha disminuido su posición en 6 puestos en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por carlos (36 intervenciones) el 23/08/2022 01:00:42
Hola Antoni,

Adjunto un archivo de prueba pdf, la idea es que el usuario pueda seleccionar el archivo que el desee.

Bendiciones
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
sin imagen de perfil
Val: 37
Ha disminuido su posición en 6 puestos en Excel (en relación al último mes)
Gráfica de Excel

Obtener datos de un archivo PDF (MACRO)

Publicado por carlos (36 intervenciones) el 06/09/2022 13:54:37
Un saludo Antoni,

Espero se encuentre muy bien.

Estoy pendiente de sus comentarios.

Bendiciones
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar