Results 1 to 2 of 2

Thread: PDF To Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PDF To Excel

    Hi guys!
    I'm not sure if the excelforum website is also part of this forum or not, but I posted a question there and I'm gonna post it here as well because I'm a little short on time. Thanks for any help you guys can give.

    I'm not great with VBA but I found this article that can help quite a bit with the solution to my current problem, I just don't know how to implement the extra steps (I'll post the link to it and the location to the code that I'm referring to at the bottom of this post as reference). So I have a PDF for every item that I want to export as an excel worksheet (and put all of them into one workbook). Each PDF of the item is in its own folder, and that folder is named after the 7 digit long serial number assigned to that item (Note that the pdf names also include this number, but then include the date of that item after, example: 1234567-2018-01-01). So basically I'm just looking for a code that would iterate through all of these folders, make a worksheet for each item (and name it after the 7 digit serial number), then export the pdf and put it in that worksheet, and all within one workbook. If my explanation is a little confusing, or if I can clarify something, please ask! And thank you in advance!



    powerspreadsheets .com / pdf-to-excel-vba/
    It's about a quarter of the way down the page. It was in a screenshot so I didn't know how to copy the text and paste the code directly to here. The code is the sample macro that he uses to show how SendKeys work with Adobe. I don't have Adobe, but I have Bluebeam Revu so if this is possible to use with Bluebeam that would be great. Thanks again!

    Update:
    Hey guys,
    So I've been trying to get this figured out by myself for the past few minutes and came up with a little bit to start off, and maybe someone can just help me with this one part so I can keep chugging at it on my own. This is all of what I have so far (maybe someone sees a better way of doing this and can recommend changes, I don't really know what I'm doing, I'm just copying from different sources and trying to splice together other people's code):

    Code:
    <code style="font-family: monospace; font-style: normal; font-weight: 400; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">Sub PDFToExcel()
    
    Dim RevuPath As String
    Dim T1FilePath As String, T2FilePath As String, T3FilePath As String
    Dim objFSO As Object
    Dim objT1Folders As Object, objT2Folders As Object, objT3Folders As Object
    Dim objT1Folder As Object, objT2Folder As Object, objT3Folder As Object
    Dim shellPath As String
    Dim T1FolderCount As Long, T2FolderCount As Long, T3FolderCount As Long
    Dim ws As Worksheet
    
    RevuPath = "Revu.exe" 'Just a placeholder for the filepath
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    T1FilePath = "\\T1 Folder" 'Just a placeholder for the filepath
    Set objT1Folders = objFSO.GetFolder(T1FilePath).SubFolders
    
    T1FolderCount = objT1Folders.Count
    
    If T1FolderCount > 0 Then
        For Each objT1Folder In objT1Folders
            Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
            ws.Name = objT1Folder.Name
            shellPath = RevuPath & " """ & T1FilePath & " """ & objT1Folder.Name & " """ & 'THIS IS WHERE IM STUCK
    
    Call Shell( _
        pathname:=shellPath, _
        windowstyle:=vbNormalFocus)
        
    Application.Wait Now + TimeValue("0:00:10")
    
    End Sub</code> 
    If you see the comment "THIS IS WHERE IM STUCK" on the shellPath line, I'm trying to figure out how to put in the name of the pdf files for the items. So to layout what the file structure looks like again, just as an example: All Items Folder > Type Folder (This folder path is noted as T# in the code, where the # is the number type it is, 1, 2 or 3) > Item Folder (This folder is the serial number of the item), and then in each item folders is the pdf, where the names of the pdfs are the serial number followed by the date of the item (example: 1234567-2018-01-01). How can I get the code to see the shellPath variable as the PDF if the dates at the end of each are different? Thanks for your help
    Last edited by Jetx227; Jun 20th, 2019 at 04:55 PM.

  2. #2
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,290
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: PDF To Excel

    Quote Originally Posted by Jetx227 View Post
    I'm not sure if the excelforum website is also part of this forum or not, but I posted a question there and I'm gonna post it here as well because I'm a little short on time.
    As per Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-a...rum-rules.html, kindly provide a link to your cross-post at Excel Forum. And, as per Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html, you should likewise provide a link there to your cross-post here.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •