-1

How 'bout taking those pdf-formatted package files from the table in Access and movin' 'em to a folder

If ya can, exteract the (product name) 'n the (pdf) as well That'd be awesome; lemme know if there's a fix; I'd be t.

this for example

enter image description here

This is the file: the one I created as an example. If you can, please check it and let me know the issue with this file. Also, guide me on the command needed to extract the PDF files

I used this code in VBA; Did not answer ; If another way other than coding also works; say thank you

Sub ExportPDFs()
    Dim rs As DAO.Recordset
    Dim filePath As String
    Dim fileNum As Integer
    
    
    
    Set rs = CurrentDb.OpenRecordset("SELECT pakage FROM Table1 ")
    
    If rs.RecordCount > 0 Then
        filePath = "C:\Users\AliReza\Desktop\folder\"
        
        rs.MoveFirst
        Do Until rs.EOF
            fileNum = FreeFile
            Open filePath & rs.Fields("pakage") & ".pdf" For Output As #fileNum
            Put #fileNum, , rs.Fields("Package").Value
            Close #fileNum
            
            rs.MoveNext
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Alireza Daneshmayeh
  • 21
  • 5
  • Please do not include external links to your personal google drive in your question. – braX Aug 17 '23 at 11:25
  • Does code work? If not, what happens - error message, wrong result, nothing? – June7 Aug 17 '23 at 11:55
  • Open filePath & rs.Fields("Package") & ".pdf" For Output As #fileNum | this error is : item not found in this collection | While this name is inside the table @june7 – Alireza Daneshmayeh Aug 17 '23 at 12:11
  • Open filePath & rs.Fields("pakage") & ".pdf" For Output As #fileNum | I write the name of the column which is (pakage); It displays this error | . pakage => erore is : bad file name or number – Alireza Daneshmayeh Aug 17 '23 at 12:14

1 Answers1

0

First, you need two different columns for the name of the document (let's call it Name) and for the OLE Object (Package) storing the document.

Then you can use an ADODB.Stream for storing the OLE Object to a file.

Sub ExportPDFs()
    Dim rs As DAO.Recordset
    Dim folder As String, path As String
    Dim adoStream As Object 'Late bound ADODB.Stream
    
    folder = "C:\Users\AliReza\Desktop\folder\"
    Set rs = CurrentDb.OpenRecordset("SELECT Name, Package FROM documents")
    Do Until rs.EOF
        path = folder & rs!Name & ".pdf"
        
        Set adoStream = CreateObject("ADODB.Stream")
        adoStream.Type = 1 'adTypeBinary
        adoStream.Open
        adoStream.Write rs("Package").Value
        adoStream.SaveToFile path, adSaveCreateOverWrite
        adoStream.Close
        
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

See: @ErikA's approach using ADODB.Stream.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • This doesn't work for OLE package objects, which I assume the OP has since they're marked "package" in table view. These objects can contain multiple files with file names, and require a more complex approach to extracting data. Best just use Lebans OleToDisk for these. – Erik A Aug 17 '23 at 13:04
  • I made a test where I inserted a PDF into a OLE Object column manually. The entry displays as "Package" and the code above works. The question is whether there is a way to extract the original name of the PDF from this OLE Object? – Olivier Jacot-Descombes Aug 17 '23 at 13:23
  • That's odd, are you sure that PDF is unchanged and still readable? You can certainly write the binary contents of an OLE field to a file, but it should contain the OLE headers and compressed file data, not the original contents. If you have raw binary data in an OLE field, it should read "Long Binary Data", not "Package", and you can't create these through the GUI afaik – Erik A Aug 17 '23 at 13:46
  • The PDF's size has indeed changed and is 552 bytes lager than the original; however I can open and display it. I tried in Firefox and PDF Architect 7. – Olivier Jacot-Descombes Aug 17 '23 at 14:03
  • A binary editor shows that the new document has indeed a Package header (and footer). Obviously the mentioned applications can open packaged PDF's . The package header does contain the original file path. So, it should indeed be possible to get the document name out of it. – Olivier Jacot-Descombes Aug 17 '23 at 14:13
  • Thank you very very much that was perfect ; I spent 9 hours today; I coded; I asked the artificial intelligence And when I do this; I asked the stack site; But I asked the wrong question I corrected my question; You guided me; fixed Thank you _ @Olivier Jacot-Descombes – Alireza Daneshmayeh Aug 17 '23 at 18:39
  • How do I change this code? to remove the table that is in the accese file at once; and save it in the folder | I'd be very grateful ; If you change this part of the code | Set rs = CurrentDb.OpenRecordset("SELECT name , pakage FROM Table1 _ @Olivier Jacot-Descombes – Alireza Daneshmayeh Aug 17 '23 at 20:25
  • In one file there are 8 to 10 tables; If it's not difficult, please guide me I don't want to take your time _ @OlivierJacot-Descombes – Alireza Daneshmayeh Aug 17 '23 at 20:28
  • See: [DROP statement (Microsoft Access SQL)](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/drop-statement-microsoft-access-sql) or [TableDefs.Delete method (DAO)](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/tabledefs-delete-method-dao) or [DoCmd.DeleteObject method (Access)](https://learn.microsoft.com/en-us/office/vba/api/access.docmd.deleteobject). – Olivier Jacot-Descombes Aug 18 '23 at 09:41