Posts by mardro

    Re: List of worksheets in the folder


    Quote

    Don't have the xlsm Workbook that contains the above code in the same folder as the one your searching on..


    If I take the workbook out of the folder which is searched the code works perfectly. Thanks a lot for it. Is it possible to modify the macro that the workbook can be in the searched folder? If not, do not worry, I can live with it :)


    Quote

    ...do you realise that the INDIRECT formula does NOT work on closed workbooks? It would be a shame to do all this work only to discover you can't do what you originally wanted to do. If you do know this then all good.


    Yes I am aware of this. I will need to open some workbooks to make it working

    Re: List of worksheets in the folder


    Thanks apo for your code. I had to change extension in the code as it did not pick up any file. When I change it to *.xlsx the code works fine, but if I change it to *.xlsm or *.xls* (as there are *.xlsm files in the folder as well) the macro closes the current workbook without any error. Any idea what is wrong?

    Re: List of worksheets in the folder


    I have uploaded two pictures in post #5 (untitled.jpg and untitled2.jpg). The first one is a warning about macro and if I click OK that I can see the second picture "Run time error". On the second warning the debug button is not active. Sorry but not sure what do you mean that is not the picture you saw last night.
    Anyway I have tried to modify the macro by my own, but I have just basic knowledge about this and I cannot understand how this macro works. I think that is nothing wrong with your code, just when it open the workbooks it also starts the macros from Workbook_Open() section. However the macro I have posted check the worksheet names in other way (without opening the file) so that is way I do not see the warning messages.
    If it possible to modify the initial macro I would be very grateful. Below is just the code from the file I have uploaded in the first post.


    Re: List of worksheets in the folder


    Sorry for not being helpful, but as you can see the "Debug" button is not active so I cannot check the error. Maybe the error came from the workbook that your code try to open, as I have mentioned before there is access query which wants to be updated. The code I have posted works fine in this case, but I need to modify the format it receive the list of the worksheet.

    Re: List of worksheets in the folder


    Thanks a lot for your help skywriter. Generally your code works as I wanted, however I receive many warning messages when it try to open the workbooks. Please see the pictures below, not sure if this is because the worksheets contain access query or they are protected. I think the code I have posted work in different method and then there is not issue like this. Would it be possible to modify the initial code to get the same results?


    [ATTACH=CONFIG]69240[/ATTACH][ATTACH=CONFIG]69241[/ATTACH]

    Re: List of worksheets in the folder


    I really mean worksheets. In my case there will be a few workbooks in the folder, each contains a few worksheets. In each workbook some worksheets will be the same with standard data so I would like to exclude them, not sure about the name now, but this will be like "Data1" and "Data2". The names of the standard worksheets will not be changed often so when necessary I can modify the macro.

    Hi All, I need to create a list of worksheet in the currrent folder and then I will use the list for Indirect formula. I have found the code to create the list, but the format of the list which the code generates is not what I need. I would like to have list of the all worksheets in the first column and path to the workseets in the second column so it can be used for Indirect formula. Please can you help me to modify the code as I do not fully understand it. Also if possible I would like to exclude some worksheets from the list eg. "sample1" and "sample2".