Convert PDF document to Excel and save as separate files

[fusion001]

Hi...I have a pdf document which has several tables with titles and headings. Each table is a department. I need to convert this into excel and keep the formatting.

Ideally I want to save each table (department) as an individual excel file, the filename will be the department code.
If this is not straightforward, then the other option is to covert the pdf document as one excel workbook but with the same formatting so each table is shown with the headings etc.

I tried to open the pdf file in word then copy & paste it into excel but doesn't work very well.

There are over 60 pages on the pdf document.

I did try a vba code which converted the pdf to excel but it loses the formatting on the subsequent pages.
This vba code is for multiple pdf files but at the moment I have just the one file.

Sub PDFtoExcel()

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Sheet1")

Dim pdf_path As String
Dim excel_path As String

pdf_path = setting_sh.Range("A1").Value
excel_path = setting_sh.Range("A2").Value

Dim fso As New FileSystemObject
Dim fo As folder
Dim f As File


Set fo = fso.getfolder(pdf_path)

Dim wa As Object
Dim doc As Object
Dim wr As Object

Set wa = CreateObject("word.application")

wa.Visible = True

Dim nwb As Workbook
Dim nsh As Worksheet

For Each f In fo.Files
    Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
    Set wr = doc.Paragraphs(1).Range
    wr.wholestory
    
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy

nsh.Paste
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

doc.Close False
nwb.Close False
Next

wa.Quit

      
End Sub

I'd appreciate some assistance with this please. Thanks

[Arnoutdv]

With formatting you mean fonts/colors/allignments/number formats?
All online tips and sample are for just getting the data and be happy.

Maybe try some existing service and test whether they also include formatting of tables:
https://pdftables.com/
https://www.pdf2go.com/pdf-to-excel

[Zvoni]

Or use pdftotext
https://www.xpdfreader.com/pdftotext-man.html
it has an option "-table"