Compile Error On: Scripting.FileSystemObject

  • Hello all,


    I have recently searched the forum and found the following post:
    http://www.ozgrid.com/forum/showthread.php?t=54151&highlight=display+folder+contents
    which is something that I am wanting to use in my spreadsheet.


    When using this, i get a compile error on:

    Code
    FSO As Scripting.FileSystemObject


    The code I am using is the following:


    This has been placed in the Sheet1 and i use a button to run the Macro.


    Can anyone help? Thanks

  • Re: Search folder and display files


    Make sure you add a reference to the Microsoft Scripting Runtime
    Usually under C:\WINDOWS\System32\Scrrun.dll
    Go to Tools, References, Browse in your VBE menu


    Ger


    [edit] too slow....

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Search folder and display files


    Aha, just added the Microsoft Scripting runtime, you were right with the dll name.


    Your bloody good man, very good.


    Any ideas how to get it to make a link to the file, I tried


    Code
    Cells(r, 9).Hyperlinks.Add , Address:=FileItem.Path & FileItem.Name


    but guess what, it didnt work.

  • Re: Search folder and display files


    Try -

    Code
    ActiveSheet.Hyperlinks.Add ActiveSheet.Cells(r, 9), Address:=FileItem.Path & FileItem.Name


    Just be careful - because I'm not sure if the resulting link will work - it might be missing "\" between the path and filename. In which case


    Code
    ActiveSheet.Hyperlinks.Add ActiveSheet.Cells(r, 9), Address:=FileItem.Path & "\" & FileItem.Name


    migh work
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Search folder and display files


    Thanks Ger,


    Had to amend it slightly to:


    Code
    ActiveSheet.Hyperlinks.Add ActiveSheet.Cells(r, 9), Address:=FileItem.Path


    but it works prefectly.


    Just a little niggle now, is there any way of making it have the link open in a new window, like the HTTP files use Target="_new"?


    Thanks

  • Re: Search folder and display files


    Do you mean when you click on the hyperlink in the cell, it launches in a new window?


    I'm not sure - there is a "FollowHyperlink" event in the worksheet which is probably triggered when you click on the hyperlink, but I'm not sure if you can change the behaviour of what happens when the link is clicked.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Search folder and display files


    richardcranney, you have been warned about the forum rules and you assured me you had read, understood and will adhere to them. Now you are AGAIN violating them.


    Quote

    It is one question per Thread and that question MUST have a Thread Title in accordance with rule 1. If you find any consequent questions come about (as they often do) and they do not fit under the current Thread Title, start a New Thread with a Thread Title in accordance with rule 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!