Visual Basic coding, Cells to Form Fields export
Tagged: data, export, forms, visual basic
Visual Basic coding, Cells to Form Fields exportPosted 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:
‘ 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.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
‘ Save and close the PDF
‘ Clean up
Set doc = Nothing
Set fd = Nothing
Set app = Nothing
End SubIsaac Harned replied 4 months, 1 week ago 6 Members · 40 Replies
David CutlerMemberDecember 29, 2022 at 11:07 amPoints: 18,260Rank: 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 McLeanMemberDecember 31, 2022 at 2:58 pmPoints: 7,776Rank: 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 DeGrootOrganizerJanuary 4, 2023 at 10:47 amPoints: 17,562Rank: 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 CutlerMemberJanuary 4, 2023 at 2:57 pmPoints: 18,260Rank: 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 McLeanMemberJanuary 4, 2023 at 2:39 pmPoints: 7,776Rank: 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 McLeanMemberJanuary 4, 2023 at 2:58 pmPoints: 7,776Rank: 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.
Points: 2,426Rank: UC2 Brainery Yellow Belt IIII
I will have to look into this, may be a few days though.
Points: 2,426Rank: UC2 Brainery Yellow Belt IIII
Sorry for the delay, I seem to be creating a mountain of volunteer projects 😬.
David CutlerMemberJanuary 5, 2023 at 8:09 amPoints: 18,260Rank: 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 LarsenMemberJanuary 5, 2023 at 2:44 pmPoints: 3,865Rank: 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 LarsenMemberJanuary 5, 2023 at 2:46 pmPoints: 3,865Rank: 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 LarsenMemberJanuary 5, 2023 at 5:49 pmPoints: 3,865Rank: 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 HarnedMemberJanuary 5, 2023 at 6:26 pmPoints: 2,426Rank: 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 HarnedMemberJanuary 5, 2023 at 6:34 pmPoints: 2,426Rank: 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 LarsenMemberJanuary 5, 2023 at 6:38 pmPoints: 3,865Rank: UC2 Brainery Orange Belt II
@isaac-harned It’s under Tools –> Form –> Export Data
Isaac HarnedMemberJanuary 5, 2023 at 6:39 pmPoints: 2,426Rank: UC2 Brainery Yellow Belt IIII
Ah for bluebeam, yes, but unfortunately I am trying Mitch’s suggestion of saving Excel as FDF
Liz LarsenMemberJanuary 5, 2023 at 6:42 pmPoints: 3,865Rank: UC2 Brainery Orange Belt II
Ah, ok. Out of my depth here 😅
Mitch YoungsMemberJanuary 5, 2023 at 6:31 pmPoints: 313Rank: UC2 Brainery Newbie
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 HarnedMemberJanuary 5, 2023 at 6:38 pmPoints: 2,426Rank: 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 YoungsMemberJanuary 5, 2023 at 6:44 pmPoints: 313Rank: UC2 Brainery Newbie
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 YoungsMemberJanuary 5, 2023 at 6:48 pmPoints: 313Rank: UC2 Brainery Newbie
I’m just trying to figure out what code to send to you that would be most helpful.
Mitch YoungsMemberJanuary 5, 2023 at 7:05 pmPoints: 313Rank: UC2 Brainery Newbie
Here is a screen recording of what my code does and my workflow if that helps.
Troy DeGrootOrganizerJanuary 6, 2023 at 8:43 amPoints: 17,562Rank: 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 YoungsMemberJanuary 6, 2023 at 1:18 pmPoints: 313Rank: UC2 Brainery Newbie
I got it to upload.
Troy DeGrootOrganizerJanuary 6, 2023 at 1:34 pmPoints: 17,562Rank: 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 YoungsMemberJanuary 6, 2023 at 2:00 pmPoints: 313Rank: UC2 Brainery Newbie
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 LarsenMemberJanuary 6, 2023 at 4:43 pmPoints: 3,865Rank: 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 LarsenMemberJanuary 6, 2023 at 11:48 pmPoints: 3,865Rank: 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 HarnedMemberJanuary 6, 2023 at 4:35 pmPoints: 2,426Rank: UC2 Brainery Yellow Belt IIII
This looks awesome! I will runs some tests!!
Mitch YoungsMemberJanuary 6, 2023 at 3:44 pmPoints: 313Rank: UC2 Brainery Newbie
Mitch YoungsMemberJanuary 6, 2023 at 9:22 pmPoints: 313Rank: UC2 Brainery Newbie
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 HarnedMemberJanuary 9, 2023 at 2:32 pmPoints: 2,426Rank: 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 HarnedMemberJanuary 17, 2023 at 4:35 pmPoints: 2,426Rank: 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 LarsenMemberJanuary 17, 2023 at 4:50 pmPoints: 3,865Rank: 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 HarnedMemberJanuary 17, 2023 at 5:13 pmPoints: 2,426Rank: 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 LarsenMemberJanuary 17, 2023 at 6:01 pmPoints: 3,865Rank: 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 LarsenMemberJanuary 19, 2023 at 9:23 pmPoints: 3,865Rank: 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 HarnedMemberJanuary 20, 2023 at 9:52 amPoints: 2,426Rank: 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:
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
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)
.Title = “Select a location to save the FDF file”
.InitialFileName = fileName & “.fdf”
If .SelectedItems.Count = 0 Then
MsgBox “No location selected, file will not be saved”
fileName = .SelectedItems(1)
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
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 “1 0 obj<</Version 1.5/FDF<</F(12 TU – Portrait.pdf)/ID[<48dd2d6619f25a804876d8592bbf3bec><78c1721c7ccd2e9289e7fbfd72376444>]/Fields[” & sFileFields & “]”
f.WriteLine “<</Root 1 0 R>>”
f.Close ‘closing the file
MsgBox “FDF file has been created successfully and saved in ” & fileName
Application.ScreenUpdating = True ‘enabling screen updating
MsgBox “Error: ” & Err.Description
Application.ScreenUpdating = True ‘enabling screen updating
Liz LarsenMemberJanuary 22, 2023 at 2:43 pmPoints: 3,865Rank: 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:
- 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* 😞
- 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 HarnedMemberJanuary 22, 2023 at 6:42 pmPoints: 2,426Rank: 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!