VBA Programming in Excel

Hi All,

Hope you can help with some Excel VBA

I'm trying to build a Macro to retrieve the File Path of a file that is within a specified Folder Path that is currently in Cell A1 ( Full Folder path is in Cell A1) and place the retrieved file path within B1 ( There will only ever be 1no. file with the folder at any one time).

then do the same for A2 with the File path in B2 etc. ( note : The folder path in A2 will be different to A1 and then hence will be a different File path in B2)

I have approximately 500 Rows within excel to do this with.

I have tried some coding myself and happy to share but i do feel I'm already heading down the wrong road.

Hope i have managed to explain this correctly and many thanks in advance for any help.

Thanks

Juicy2052

Hello Jolivanes,

That works a treat, but i was trying to retrieve the File path in B2 etc. rather than the file name.

The drive letter is included within Column A and I'm actually the file type is a pdf

What would i need to change to retrieve the file path ?

Thanks

Juicy2052

Hi Jolivanes

Just to clarify

The path in Column A is the Folder Path and I'm looking for the File Path that is in side the Folder of Column A to be put in column B


Many Thanks

Juicy2052

Hi Jolivanes

Hopefully i have done this correct and I should of uploaded the example workbook.

I can get the file path by the way of a manual search ie. through Dialog but the idea is to have the folder path in one column and the File path of the file inside the folder in another column.

Then hyperlinks will be created to each location etc.

Hope this helps

Juicy 2052

Hi Jolivanes

Yes that's what I'm looking for combined with your previous code so i have now the below which runs perfectly in the Test File, however, as soon as i apply and run in the actual file i get a Run-time error 5, Invalid procedure call or argument ?

HTML Code: 

The piece of code causing the issue is the two lines of Hyperlink Code as below, In the actual file its running through 243 Rows.

HTML Code: 

I like the idea of always inserting the Hyperlinks as it always refreshes the link, the file will be replaced say once a year and may be a different file Name etc. , the folder will always remain.

Why do you think im getting the run-time error 5 ?

Hi Jolivanes

Yes that's what I'm looking for combined with your previous code so i have now the below which runs perfectly in the Test File, however, as soon as i apply and run in the actual file i get a Run-time error 5, Invalid procedure call or argument ?

Please Login or Register  to view this content.

The piece of code causing the issue is the two lines of Hyperlink Code as below, In the actual file its running through 243 Rows.

Please Login or Register  to view this content.

I like the idea of always inserting the Hyperlinks as it always refreshes the link, the file will be replaced say once a year and may be a different file Name etc. , the folder will always remain.

Why do you think im getting the run-time error 5 ?