How to add pdfsharp library DLL to macro excel VBA references

Question

0.00/5 (No votes)

See more:

I have the following code, however, to run it, the prerequisite is to have the PDFsharp library installed and I'm not able to add the reference in Excel VBA.

Sub BulkPDFExtraction()
' Reference: PDFsharp library

' Define the folder path containing the PDF files
Dim folderPath As String
folderPath = "C:\Users\vlad.zaicescu\Desktop\PDF-to-Excel\sample_invoice"

' Create a new Excel workbook and worksheet
Dim wb As Workbook
Set wb = Workbooks.Add

Dim ws As Worksheet
Set ws = wb.Sheets(1)

' Set the starting row for inserting the extracted data
Dim startRow As Integer
startRow = 1

' Get all PDF files in the folder
Dim pdfFiles() As String
pdfFiles = GetPDFFilesInFolder(folderPath)

' Extract data from each PDF file and insert into Excel
Dim i As Integer
For i = LBound(pdfFiles) To UBound(pdfFiles)
Dim pdfPath As String
pdfPath = folderPath & pdfFiles(i)

Dim extractedData As String
extractedData = ExtractDataFromPDF(pdfPath)

' Insert the extracted data into the Excel worksheet
ws.Cells(startRow, 1).Value = pdfFiles(i) ' Insert the PDF file name
ws.Cells(startRow, 2).Value = extractedData ' Insert the extracted data

startRow = startRow + 1
Next i

' Auto-fit the columns in the Excel worksheet
ws.Columns.AutoFit

' Save the workbook
Dim savePath As String
savePath = "C:\Path\to\save\output.xlsx"

wb.SaveAs savePath

' Close the workbook and clean up
wb.Close
Set ws = Nothing
Set wb = Nothing

MsgBox "Bulk extraction completed. Output saved to: " & savePath
End Sub

Function GetPDFFilesInFolder(ByVal folderPath As String) As String()
' Get all PDF files in the specified folder

Dim files() As String
Dim fileCount As Integer
fileCount = 0

Dim fileName As String
fileName = Dir(folderPath & "*.pdf")

Do While fileName <> ""
ReDim Preserve files(0 To fileCount)
files(fileCount) = fileName

fileCount = fileCount + 1
fileName = Dir
Loop

GetPDFFilesInFolder = files
End Function

Function ExtractDataFromPDF(ByVal pdfPath As String) As String
' Extract data from a PDF file using PDFsharp

' Create a PDF document
Dim document As New PdfSharp.Pdf.PdfDocument

' Open the PDF file
document.Open(pdfPath)

' Extract the text from each page
Dim extractedData As String
extractedData = ""

Dim pageCount As Integer
pageCount = document.PageCount

Dim i As Integer
For i = 0 To pageCount - 1
Dim page As PdfSharp.Pdf.PdfPage
page = document.Pages(i)

Dim content As PdfSharp.Pdf.Content.PdfContentReader
content = New PdfSharp.Pdf.Content.PdfContentReader(page)

While content.Read()
If content.GetType() Is GetType(PdfSharp.Pdf.Content.PdfLiteral) Then
Dim literal As PdfSharp.Pdf.Content.PdfLiteral
literal = DirectCast(content, PdfSharp.Pdf.Content.PdfLiteral)

extractedData = extractedData & literal.Value
End If
End While
Next i

' Close the PDF document
document.Close

ExtractDataFromPDF = extractedData
End Function

What I have tried:

I have created a class in visual studio, installed the PDFsharp library through NuGet package manager and the build the solution to receive a DLL that I can use as a reference in Excel VBA. However, when I try to add the DLL I cannot. Furthermore, I tried just adding the PDFsharp DLL from the NuGet package, which also failed.
Please see the repro video here: https://app.screencast.com/MJmEkYKUsnGsw

Posted 29-Dec-23 10:08am

VladZaicescu

Add a Solution

Comments

Dave Kreskowiak 29-Dec-23 16:25pm

Did you write your .DLL to be exposed through COM? If not, VBA cannot use it.

Also, just creating a Class Library project and adding PDFSharp to it is not going to work. You MUST write a bunch of code, with classes and methods exposed through COM, and a wrapper around the PDFSharp functionality you want to use. VBA cannot directly interact with PDFSharp because that library itself is not a COM server.

https://learn.microsoft.com/en-us/dotnet/core/native-interop/expose-components-to-com

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M-Badger · Accepted Answer · 2023-12-30T01:05:00

To learn the basics start with a really simple example and once you know how to make that work then you can start the more complex implementation you outlined above. There are lots of examples out there, here's once :

Create a new Class Library project

Make the Class COM-Visible:

In the class that you want to expose, add the ComClass attribute to make it COM-visible.

VB.NET

Imports System.Runtime.InteropServices

<ComClass(MyClass.ClassId, MyClass.InterfaceId, MyClass.EventsId)>
Public Class MyClass

    Public Const ClassId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
    Public Const InterfaceId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
    Public Const EventsId As String = "XXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"

    ' Add your methods and properties here

End Class

Note: You need to replace the ClassId, InterfaceId, and EventsId with your own GUIDs. You can generate these GUIDs using Visual Studio's "Create GUID" tool.

Write Methods and Properties:

Add the methods and properties you want to expose through COM in your class.

VB

Public Function AddNumbers(ByVal a As Integer, ByVal b As Integer) As Integer
    Return a + b
End Function

Register for COM Interop:

In the project properties, go to the "Compile" tab and check the "Register for COM Interop" option.

Build the Project

Excel Add-In Configuration:

In Excel, go to "Developer" -> "Excel Add-ins" -> "Browse" and select the DLL file generated in the bin folder of your project.

Testing:

Now you can use your class library from Excel as an add-in. In a cell, you can use a formula like =MyClass.AddNumbers(3, 4) to get the sum.