Use Excel VBA to extract fund size data

[peixin]

Hi i need to extract fund size data from fund factsheet.

Sample URL:

https://docs.publifund.com/12_FACT/LU1820811914/en_SG

Anyone can guide me how to create VBA to auto extract fund size data?

Thank you.

[arnelgp]

can you download the pdf and use Able2Extract to extract the info?

[peixin]

No, im using company computer so unable to install other app. And i actually have around 2-300 pdf url to extract fund size

[June7]

Have you web searched topic "Access VBA extract data from PDF"? https://www.access-programmers.co.uk/forums/threads/extract-data-from-a-pdf-into-access.203947/

[peixin]

Yes, I actually tried a few lines of code, but because I am using Adobe Reader and not Adobe Acrobat Pro, all the VBA code failed.

[June7]

True, requires Adobe Acrobat Pro or 3rd party tool.

Since you say cannot install any new software, SOL.

If this is a company task then bosses need to authorize purchase of tool to accomplish.

[p45cal]

Power Query:
let
Source = Pdf.Tables(Web.Contents("https://docs.publifund.com/12_FACT/LU1820811914/en_SG"), [Implementation="1.3"]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each Text.StartsWith([Column1], "Fund size:")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Column1"})
in
#"Removed Other Columns"

In the attached, right click the small table at cell A1 and choose Refresh.

[June7]

Will the powers that be allow you to install Power Query add-in?

I tested the posted Excel and got error.
"[Expression.Error] The import Pdf.Tables matches no exports. Did you miss a module reference?"

And under "Workbook Queries" displays "Download did not complete."

I tried right click on A1 and A2.

[p45cal]

Will the powers that be allow you to install Power Query add-in?Excel 2010 and Excel 2013 would require that add-in to be installed, later versions of Excel from 2016 have it built-in under the guise of Get & Transform Data.
The OP hasn't revealed his version of Excel.

You can check if you've got the Pdf.Tables function in your version of Excel by running the query at the bottom (Section 5) of this page: https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-the-import-xxx-matches/8335bba2-9c7b-47d8-a5d5-1ece1c9f9c4a

[June7]

I have Excel 2010 and that function does not list in that query output. Version: 2.62.5222.701 32-bit

The version UPDATE button is greyed out.

[p45cal]

I have Excel 2010 and that function does not list in that query output.
If @peixin had responded we'd know if this worked for him or not.