Visual Basic coding, Cells to Form Fields export

  • Visual Basic coding, Cells to Form Fields export

    Posted by Isaac Harned on December 27, 2022 at 8:50 am

    Looking to export excel data directly into form fields in Bluebeam, but can’t get it to call Revu properly. Here is my code so far:

    Sub TransferDataToPDF()

    ‘ Declare variables

    Dim app As RevuLauncherLib.IOpenDocuments3

    Dim doc As Object

    Dim ws As Excel.Worksheet

    Dim fd As Object

    Dim filePath As String

    ‘ Set variables

    Set ws = ThisWorkbook.Worksheets(“Sheet1”)

    ‘ Show file open dialog box

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    ‘ Set the file filter to display only PDF files

    fd.Filters.Clear

    fd.Filters.Add “PDF Files”, “*.pdf”

    ‘ Display the file open dialog box

    If fd.Show = -1 Then

    ‘ Get the selected file path

    filePath = Dir(fd.SelectedItems(1))

    ‘ Open the PDF in Bluebeam Revu

    Set doc = app.EditDocument(filePath, 0)

    ‘ Transfer data from the Excel workbook to the PDF form fields

    For Each formField In doc.Forms

    ‘ Get the name of the current form field

    fieldName = formField.Name

    ‘ Get the corresponding value from the Excel workbook

    FieldValue = ws.Sheets(“Sheet1”).Range(fieldName).Value

    ‘ Set the value of the form field in the PDF file

    formField.Value = FieldValue

    Next

    End Sub

    ‘ Save and close the PDF

    doc.Save

    doc.Close

    ‘ Clean up

    Set doc = Nothing

    End If

    Set fd = Nothing

    Set app = Nothing

    End Sub

    Isaac Harned replied 4 months, 1 week ago 6 Members · 40 Replies
  • 40 Replies
  • David Cutler

    Member
    December 29, 2022 at 11:07 am
    Points: 18,260
    Rank: UC2 Brainery Advanced Blue Belt III UC2 Brainery Advanced Blue Belt III

    I’m not a programer @isaac-harned but I’m very curious to see how this works out! Perhaps @lizlarsen can chime in on this one….

  • Doug McLean

    Member
    December 31, 2022 at 2:58 pm
    Points: 7,776
    Rank: UC2 Brainery Purple Belt II UC2 Brainery Purple Belt II

    Isaac, are you trying to go from Excel to PDF? Why?

    Wouldn’t it be much easier to go to either a Word or Excel form and then just print to PDF?

    Another way would be to use Power Automate to take named cells from an Excel document to a PDF.

  • Troy DeGroot

    Organizer
    January 4, 2023 at 10:47 am
    Points: 17,562
    Rank: UC2 Brainery Advanced Blue Belt II UC2 Brainery Advanced Blue Belt II

    I’d love to see what your end goal is here @isaac-harned I have things I would like to do pulling data from Excel to form fields in a PDF as well.

    • David Cutler

      Member
      January 4, 2023 at 2:57 pm
      Points: 18,260
      Rank: UC2 Brainery Advanced Blue Belt III UC2 Brainery Advanced Blue Belt III

      I’m wondering the same thing here @isaac-harned – what is the end product that you are trying to achieve?

  • Doug McLean

    Member
    January 4, 2023 at 2:39 pm
    Points: 7,776
    Rank: UC2 Brainery Purple Belt II UC2 Brainery Purple Belt II

    It seems going from PDF data to Excel is fairly easy, but going the other way is almost impossible.

    I would still explore the use of Power Automate though, might be the easiest way

  • Doug McLean

    Member
    January 4, 2023 at 2:58 pm
    Points: 7,776
    Rank: UC2 Brainery Purple Belt II UC2 Brainery Purple Belt II

    I just looked and there is a Get PDF Form Data connector in Power Automate.
    Its put out by Encodian.
    I’ve never used this, but I’m sure with a little digging, we could figure it out pretty easily.

    • Isaac Harned

      Member
      January 4, 2023 at 5:21 pm
      Points: 2,426
      Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

      I will have to look into this, may be a few days though.

  • Isaac Harned

    Member
    January 4, 2023 at 5:21 pm
    Points: 2,426
    Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

    Sorry for the delay, I seem to be creating a mountain of volunteer projects 😬.

    Long story short, there is a customizable PDF form that I am creating (discussion is here: https://uchapter2.com/brainery/javascript/forum/topic/isaac-harned-here-for-forms-with-javascript/#post-135307), but not everyone in the office enjoys entering data on the PDF, which is understandable with the loss of keyboard flexibility. Once it’s in the field it is actually much more workable on ipad. As an option for the office, I am basically creating an input form.

    • David Cutler

      Member
      January 5, 2023 at 8:09 am
      Points: 18,260
      Rank: UC2 Brainery Advanced Blue Belt III UC2 Brainery Advanced Blue Belt III

      Interesting issue you are tackling there @isaac-harned

      We have a small operation so I’m the only one using my custom tools at this point so I only have to keep myself happy with how they work. Preparing something for others to use and be productive with is and entirely different challenge! πŸ™‚

  • Liz Larsen

    Member
    January 5, 2023 at 2:44 pm
    Points: 3,865
    Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

    I’m looking into this. I have a secret resource that can probably shed some light on this.

    I’ll report back when I know more.

    • Liz Larsen

      Member
      January 5, 2023 at 2:46 pm
      Points: 3,865
      Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

      Actually, I’m being silly. @isaac-harned , my resource is your resource.

      I’m reaching out to Mitch. Hopefully you didn’t already try that avenue.

    • Liz Larsen

      Member
      January 5, 2023 at 5:49 pm
      Points: 3,865
      Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

      @isaac-harned Mitch says:

      Quickest way [to bring data into a PDF] is to create a FDF with Excel and then to open it with Bluebeam. How I deal with a large amount of the same forms are to copy and rename the PDF form template with Excel and create all of the FDF files in the same folder. I then open all of the FDF files then close Bluebeam and save on close. The whole process takes about 2 min to do for 150 or so PDFs. Best way I’ve found to go from Bluebeam to Excel is CSV, and FDF is the best way to go from Excel to Bluebeam.

      I hope this helps!

      • Isaac Harned

        Member
        January 5, 2023 at 6:26 pm
        Points: 2,426
        Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

        Mr. Young coming in clutch!

        Thank you @mitchyoungs will try this out. I don’t know why I did not try this myself πŸ˜…

        • Isaac Harned

          Member
          January 5, 2023 at 6:34 pm
          Points: 2,426
          Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

          Maybe I am missing it, or maybe its a type of export that I didn’t see in the data tab, but these are the options I have for Save as file type:

          • Liz Larsen

            Member
            January 5, 2023 at 6:38 pm
            Points: 3,865
            Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

            @isaac-harned It’s under Tools –> Form –> Export Data

            • Isaac Harned

              Member
              January 5, 2023 at 6:39 pm
              Points: 2,426
              Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

              Ah for bluebeam, yes, but unfortunately I am trying Mitch’s suggestion of saving Excel as FDF

            • Liz Larsen

              Member
              January 5, 2023 at 6:42 pm
              Points: 3,865
              Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

              Ah, ok. Out of my depth here πŸ˜…

  • Mitch Youngs

    Member
    January 5, 2023 at 6:31 pm
    Points: 313
    Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

    I have a few questions on what you want to do.

    Are the form fields the same every time you see the Pdf?

    Are you creating the form/forms initially or is there information you need to preserve in form fields?

    If the flow just from Excel -> Bluebeam or do you need to pull information from the form later?

    • Isaac Harned

      Member
      January 5, 2023 at 6:38 pm
      Points: 2,426
      Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

      – All field names consistent across templates.

      – Form will be blank when importing.

      – No reverse necessary, once it’s in the pdf it’s good. These data files will be used year over year and the transfer from PDF to new version of PDF is easy enough

      • Mitch Youngs

        Member
        January 5, 2023 at 6:44 pm
        Points: 313
        Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

        Do you need to create multiples of this form at one time?

        The way mine is set up i generate multiple pdf’s (about 150ish) from a template and rename them with vba then create all of the fdf files with vba that match the pdf’s.

        • Mitch Youngs

          Member
          January 5, 2023 at 6:48 pm
          Points: 313
          Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

          I’m just trying to figure out what code to send to you that would be most helpful.

          • Mitch Youngs

            Member
            January 5, 2023 at 7:05 pm
            Points: 313
            Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

            Here is a screen recording of what my code does and my workflow if that helps.

            • Troy DeGroot

              Organizer
              January 6, 2023 at 8:43 am
              Points: 17,562
              Rank: UC2 Brainery Advanced Blue Belt II UC2 Brainery Advanced Blue Belt II

              Welcome to the community @mitchyoungs! I’m excited to learn from you here, your skillset if much appreciated! You have me on the edge of my seat here, were you able to add the recording? I’m also following close because I have a one-page pdf with a couple of fields I would like to populate from a spreadsheet. this being several pdfs at a time, like you.

            • Mitch Youngs

              Member
              January 6, 2023 at 1:18 pm
              Points: 313
              Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

              I got it to upload.

            • Troy DeGroot

              Organizer
              January 6, 2023 at 1:34 pm
              Points: 17,562
              Rank: UC2 Brainery Advanced Blue Belt II UC2 Brainery Advanced Blue Belt II

              This is all new to me honestly. What does the dlf file represent, a complete row of data in the excel file? Is each form field in the pdf form a column in that row? This might be a fantastic solution to a problem I didn’t know I had!

              Watch out Brainery, @mitchyoungs is coming in with guns blaze’n πŸ€£πŸ”«

            • Mitch Youngs

              Member
              January 6, 2023 at 2:00 pm
              Points: 313
              Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

              Honestly I run all of my macros from an add in I made so none of my excel documents are macro-enabled. I’m trying to make work in a zip folder to upload here so that you can open and run it without changing a bunch of code. For now here is the code that i think has all of what you would need to reference and the template pdf but i’ll upload something better in a bit.

              This file was truncated for preview. Please download to view the full file.
            • Liz Larsen

              Member
              January 6, 2023 at 4:43 pm
              Points: 3,865
              Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

              I know you all think I know this sh*t really well, but I always feel like an idiot child next to @mitchyoungs . He’s so frickin smart and I just stand back in awe like I’m sure everyone else is right now.

              And I’d like to thank @isaac-harned for literally asking the perfect question to get Mitch to join the Brainery πŸ˜‚

            • Liz Larsen

              Member
              January 6, 2023 at 11:48 pm
              Points: 3,865
              Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

              In the spreadsheet, you can see there are different groups of data. For example, 113, 113.01, 113.02, 113.03 are grouped together as 113.fdf. The VBA macro goes through this spreadsheet and separates the different groups, then takes all their respective information and creates an FDF file based on a template PDF file.

              Each FDF file is a separate group of data that tells the PDF what data to put in which form field. One FDF file for each PDF form.

              I hope that answered your question.

              This is a highly customized VBA macro doing all the heavy lifting. This macro only works with this specific spreadsheet. To modify it to work with another spreadsheet would basically require rewriting the whole thing. It’d probably only be worth it if you’re doing this a lot, like in Mitch’s case where he’s creating 150 all at once.

        • Isaac Harned

          Member
          January 6, 2023 at 4:35 pm
          Points: 2,426
          Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

          This looks awesome! I will runs some tests!!

  • Mitch Youngs

    Member
    January 6, 2023 at 3:44 pm
    Points: 313
    Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

    Try this out. I made it work with just the template file and macro-enabled excel. Tip there is javascript on formatting my fields for template so after you open the fdf hold down the tab key and it will reformat the text in each cell.

  • Mitch Youngs

    Member
    January 6, 2023 at 9:22 pm
    Points: 313
    Rank: UC2 Brainery Newbie UC2 Brainery Newbie Belt Rank

    Isaac or anyone else trying to figure out the VBA code feel free to ask questions.

    One thing I did want to mention is that this specific code has logic to figure out if it is a Bid Item (100) or sub item (100.01) so it needs to create a new sheet because it has run out of available rows. Also, I tend to use tables in excel so I can reference data by column name instead of hard coding.

    I have a simpler form with only adding a few cells of data and not much logic in it if you want me to share that one.

    • Isaac Harned

      Member
      January 9, 2023 at 2:32 pm
      Points: 2,426
      Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

      Thank you for taking a look, and for your patience with me while I put out fires. Promise I’m turning back to this ASAP!

    • Isaac Harned

      Member
      January 17, 2023 at 4:35 pm
      Points: 2,426
      Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

      OK, I am playing with this a bit today, and this could work, but I think it’s gonna take a little finagling. Just as a quick explanation: I have 12 Terminal unit sections on the page with lines built in for Grille data. Each of these lines will have fields that are named by ‘Terminal#-Row#’. I will have to find a way to concatenate this in the middle before the final merge for the FDF format, i.e. “Mark-1” in the first column will end up as “Mark1-1”. Seems like a split first, then store as separate and concatenate, not too bad. It will take me some time to get another round here but I will get back with something here semi-soon.

      Unfortunately my job consists of the highest complexity takeoffs, plus training, plus develop new workflows, so timing gets very sporadic. Thank you guys for your patience!

      • Liz Larsen

        Member
        January 17, 2023 at 4:50 pm
        Points: 3,865
        Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

        Wanna send it to me and I’ll take a look? I think I know what you mean, but I’m not 100%.

        • Isaac Harned

          Member
          January 17, 2023 at 5:13 pm
          Points: 2,426
          Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

          Sure it’s still a work in progress though. Tab one of the excel workbook was my original intent, but I abandoned after only naming a couple of fields.

          Tab 2 is the start of the table. Any row identifiers that have no number would get the column name appended to the end. All the row titles with a dash would get the column name before the dash.

          I think you are pretty familiar with the pdf form at this point =)

          • Liz Larsen

            Member
            January 17, 2023 at 6:01 pm
            Points: 3,865
            Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

            I will take a look.

            And I will make an effort not to write the whole thing.

            Who am I kidding? I will probably see it as a challenge and do it just to see if I can. But, I will only take a brief look tonight to see if I can answer your question concisely. I just finished my deep dive on Mitch’s code, so it’s all fresh in my mind right now.

      • Liz Larsen

        Member
        January 19, 2023 at 9:23 pm
        Points: 3,865
        Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

        I used Replace(str, “-“, j & “-“) and looped through j = 1 to 12 (for 12 columns) and that seemed to do the trick. You can thank ChatGPT for that idea.

        See the attached bit of code that totally works cuz I tested it 😁 I wrote it with the intent that you’ll be using Mitch’s method to output an FDF file.

        • Isaac Harned

          Member
          January 20, 2023 at 9:52 am
          Points: 2,426
          Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

          ok I am much closer now than ever before thanks to you guys and that amazing tool. Still having a couple issues:

          1. I am seeing duplicated field names when formatted to fdf, can’t quite see why this is happening. Some also appear not to add the “J” variable to the name, and there are several duplications of that.

          Also having issue with actually saving the file as FDF, as it is trying to save as xml and just adding the extension for fdf. File attached (could not upload “fdf” file, extension not supported. will have to run macro to see), here’s my code so far:

          Sub fdfExpTest()

          On Error GoTo ErrorHandler

          Application.ScreenUpdating = False ‘disabling screen updating

          Dim ws As Worksheet

          Set ws = Worksheets(“Sheet3”)

          Dim lrow As ListRow

          Dim table As ListObject

          Set table = ws.ListObjects(“Table3”)

          Dim sFileFields, formField As String

          Dim currentCell As String

          Dim fileName As String

          sFileFields = “”

          Dim i As Integer

          For i = 2 To table.ListRows.Count

          Set lrow = table.ListRows(i)

          currentCell = lrow.Range(1, 1).Address

          Dim j As Integer

          For j = 1 To 12

          formField = “<</V(” & Range(currentCell).Value & “)/T(” & Replace(Range(currentCell).Value, “-“, j & “-“) & “)>>”

          sFileFields = sFileFields & formField

          Next j

          Next i

          fileName = InputBox(“Please enter the file name:”, “File Name”)

          If fileName = “” Then Exit Sub ‘if user clicked cancel or didn’t type a name

          Dim dialog As FileDialog

          Set dialog = Application.FileDialog(msoFileDialogSaveAs)

          With dialog

          .Title = “Select a location to save the FDF file”

          .InitialFileName = fileName & “.fdf”

          .Show

          If .SelectedItems.Count = 0 Then

          MsgBox “No location selected, file will not be saved”

          Exit Sub

          Else

          fileName = .SelectedItems(1)

          End If

          End With

          If Dir(fileName & “.fdf”) <> “” Then ‘checking if the path exists

          If MsgBox(“A file with the same name already exists, do you want to replace it?”, vbYesNo) = vbNo Then Exit Sub

          End If

          Dim fso As New FileSystemObject ‘Create a new FileSystemObject

          Dim f As TextStream ‘Create a new TextStream

          Set f = fso.CreateTextFile(fileName & “.fdf”, True) ‘Create a new .fdf file and open it for writing

          f.WriteLine “%FDF-1.2”

          f.WriteLine “%ÒãÏÓ”

          f.WriteLine “1 0 obj<</Version 1.5/FDF<</F(12 TU – Portrait.pdf)/ID[<48dd2d6619f25a804876d8592bbf3bec><78c1721c7ccd2e9289e7fbfd72376444>]/Fields[” & sFileFields & “]”

          f.WriteLine “]>>endobj”

          f.WriteLine “trailer”

          f.WriteLine “<</Root 1 0 R>>”

          f.WriteLine “%%EOF”

          f.Close ‘closing the file

          MsgBox “FDF file has been created successfully and saved in ” & fileName

          Application.ScreenUpdating = True ‘enabling screen updating

          Exit Sub

          ErrorHandler:

          MsgBox “Error: ” & Err.Description

          Application.ScreenUpdating = True ‘enabling screen updating

          End Sub

          • Liz Larsen

            Member
            January 22, 2023 at 2:43 pm
            Points: 3,865
            Rank: UC2 Brainery Orange Belt II UC2 Brainery Orange Belt II

            Ok, at some point, I saved the wrong version of my code with is why problem #1 happened. It’s fixed now.

            To answer your questions:

            1. I think the duplicated field names you’re referring to were the ones without numbers on the end (BOXTAG and AirType). That’s because I saved the wrong version of my code. The attached code is now correct. I have an if statement in there to properly add the column number to the form field string, and that somehow never made it to the code I saved previously. *womp womp* 😞
            2. I ran your code and it is saving as an FDF file, it’s just adding “.xlxs” or “.xlm” to the end of the file name because you’re using the dialog box to select a save location. But, at least on my end, the file is technically an FDF file. To fix this, I added a simple Replace function in your code to remove the excel file extension. See attached screenshot.

            Also note that you removed the Offset from my code, with is essential because that’s what is looping through the columns to get their values.

            • Isaac Harned

              Member
              January 22, 2023 at 6:42 pm
              Points: 2,426
              Rank: UC2 Brainery Yellow Belt IIII UC2 Brainery Yellow Belt IIII

              I did end up figuring out the if statement after posting this, bit the offset I completely missed! Thank you, will try this out Monday! Definitely think you may have hit it perfect there!