General Questions
This is a great place to ask those general questions that don’t fit in other specific groups.
Organizer:
- Organized by
-
-
Visual Basic coding, Cells to Form Fields export
-
Visual Basic coding, Cells to Form Fields export
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
-
40 Replies
-
Points: 26,364Rank: UC2 Brainery Advanced Brown Belt
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….
-
Points: 14,863Rank: UC2 Brainery Blue Belt IIII
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.
-
Points: 23,485Rank: UC2 Brainery Brown Belt III
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.
-
Points: 26,364Rank: UC2 Brainery Advanced Brown Belt
I’m wondering the same thing here @isaac-harned – what is the end product that you are trying to achieve?
-
-
Points: 14,863Rank: UC2 Brainery Blue Belt IIII
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
-
Points: 14,863Rank: UC2 Brainery Blue Belt IIII
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: 8,053Rank: UC2 Brainery Purple Belt III
I will have to look into this, may be a few days though.
-
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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.
-
Points: 26,364Rank: UC2 Brainery Advanced Brown Belt
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! 🙂
-
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
@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!
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
Mr. Young coming in clutch!
Thank you @mitchyoungs will try this out. I don’t know why I did not try this myself 😅
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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:
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
@isaac-harned It’s under Tools –> Form –> Export Data
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
Ah for bluebeam, yes, but unfortunately I am trying Mitch’s suggestion of saving Excel as FDF
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
Ah, ok. Out of my depth here 😅
-
-
-
-
-
-
Points: 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?
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
– 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
-
Points: 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.
-
Points: 313Rank: UC2 Brainery Newbie
I’m just trying to figure out what code to send to you that would be most helpful.
-
Points: 313Rank: UC2 Brainery Newbie
Here is a screen recording of what my code does and my workflow if that helps.
-
Points: 23,485Rank: UC2 Brainery Brown Belt III
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.
-
Points: 313Rank: UC2 Brainery Newbie
I got it to upload.
-
Points: 23,485Rank: UC2 Brainery Brown Belt III
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 🤣🔫
-
Points: 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. -
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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 😂
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
-
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
This looks awesome! I will runs some tests!!
-
-
-
-
Points: 313Rank: UC2 Brainery Newbie
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.
-
Points: 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.
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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!
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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!
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
Wanna send it to me and I’ll take a look? I think I know what you mean, but I’m not 100%.
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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 =)
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
-
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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
-
Points: 4,855Rank: UC2 Brainery Orange Belt IIII
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.
-
Points: 8,053Rank: UC2 Brainery Purple Belt III
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!
-
-
-
-
Log in to reply.