TOC of Shared File Directory using VBA

  • Howdy. Big Request here. So I figured I'd better say hi first. Hope your new year is off to a good start, and you enjoyed the holidays. Mine was great: did some deer hunting, was out in the woods quite a bit, enjoyed some snow & ice, did some chainsawing, things are good.. even included climbing up on roof -- to put lights on, and take them off (still with ice and snow on roof).


    I used to have a great VBA routine that would list all the files in a specified directory, and tell me some basic information -- resulting in a table similar to what is below:


    File Name Save Date Hyperlink to File


    My original version was written for word docs only, and captured some of the properties info as well.. document title, author.. etc.. but this time I'd settle for the three listed above.


    I know it's a HUGE request, but I thought I'd beg. I don't have the talent to write something like that on my own.

  • Re: TOC of Shared File Directory using VBA


    Hi TD


    Try this

  • Re: TOC of Shared File Directory using VBA


    Pasted. What triggers it?


    HTML
    trigger.xls	17408 bytes.	Created: 1/4/2005 9:47:09 AM	Last Modified: 1/4/2005 9:47:50 AM
    Follow-Up Matrix.xls	33792 bytes.	Created: 1/3/2005 10:54:53 AM	Last Modified: 1/3/2005 10:54:54 AM
    Thumbs.db	223232 bytes.	Created: 12/8/2004 2:38:45 PM	Last Modified: 12/17/2004 11:44:18 AM



    DOH. heh.. never mind. Looks good, I think? I need to add the column headings. Otherwise, looks like I am well on my way, thanks to you!


    Will try exact file path to see what it generates.



    Ok.. really good start.. attaching excel sheet.


    Would prefer column headings.. instead of replicating "Created:" and "Last Modified."


    Some of it is just formatting the response from the macro. Some of it is making the headings (in bold on attachment) generate on row 1.

  • Re: TOC of Shared File Directory using VBA


    I think this amendment is what you want

  • Re: TOC of Shared File Directory using VBA


    This File maybe of use to you


    http://www.xcelfiles.com/Shell32_01.html



      File properties extracted from the Namespace Folder are;
      Name,
      Size,
      Type,
      Date Modified,
      Date Created,
      Date Accessed,
      Attributes,
      Status,
      Owner,
      Author,
      Title,
      Subject,
      Category,
      Pages &
      Comments.

  • Re: TOC of Shared File Directory using VBA


    Good stuff Ivan. I need to study your page a little further.


    I cleaned up my attachment from yesterday, doing some additional stuff to it today -- should be uploading improved version later today.


    Thanks to all who have lent input!


    Code
    MainFolderName = "X:\FORMS\706-ASSY"


    The next thing I want to accomplish. There are several worksheets in this workbook.


    Code
    If ActiveSheet = "706" then MainFolderName = "X:\FORMS\706-ASSY"
    If ActiveSheet = "801" then MainFolderName = "X:\FORMS\801-PRODCTRL".


    I don't know the exact syntax to accomplish the above, so looking into that now. Any help is genuinely appreciated!

  • Re: TOC of Shared File Directory using VBA


    I have updated the original attachment. Now the VBA will check to see which sheet is active, and set the subfolder appropriately.


    I also took care of the redundancy portion in the individual columns.


    I could have populated every sheet, but now that I know it works -- I will stop at this point to protect the innocent. LOL.


    Again. Thank you so much, everyone -- !!

  • Re: TOC of Shared File Directory using VBA


    A Select case would be more efficient, and you dont need to select the cell to enter the headings

  • Re: TOC of Shared File Directory using VBA



    Still an error. I'm not smart enuff to know what to look for..

  • Re: TOC of Shared File Directory using VBA


    Quote from TownDawg

    Of course it does!..


    I pasted it, replacing the old. I got a 424 error though. "needs an object?"


    Where is the error occurring?


    BTW how about using a Switch statement?

  • Re: TOC of Shared File Directory using VBA


    I think we are getting closer. New error. Should be easier to fix. "Path Not Found."


  • Re: TOC of Shared File Directory using VBA


    There might be a problem with the Switch statement.


    It isn't something I use a lot and I've maybe missed something out.


    You might want to switch back to the Select method:

  • Re: TOC of Shared File Directory using VBA


    Hi,


    There is a potentially trap in Roy's select statement, which maybe causing the problem of passing thru the select statement without actually assigning a value to MainFolderName.


    Code
    '
        Case 901 - 905 ' without the use of quotes the sheet name will actually be compared to [b]-4[/b]
    
    
        Case 901 to 905 ' if the value can be between 901 and 905
    
    
        Case "901 - 905" ' if the sheetname is actuall 901 - 905
            MainFolderName = "X:\FORMS\901-905-USP-UFP"
        End Select


    Norie, your Switch command requires a variable against which to test.

    Code
    SubFolderName = Switch(shtName = "101", "ADMIN", shtName = "211", "PURCHASING", _
            shtName = "231", "PROJ-CTRL", shtName = "301", "ACCOUNTING", _
            shtName = "321", "INFO SYSTEMS", shtName = "401", "HR AND TRAINING", shtName = "501", "QUALITY", _
            shtName = "601", "ENG-ASSY", shtName = "611", "ENG-PRESS", shtName = "621", "PLANT-MAINT", _
            shtName = "701", "SAFETY", shtName = "705", "UPS-VA", shtName = "706", "ASSY", shtName = "713", "PRESS", _
            shtName = "721", "DIE-MAINT", shtName = "801", "PROD-CTRL", shtName = "901-905", "USP-UFP")
  • Re: TOC of Shared File Directory using VBA


    Quote from Andy Pope

    Norie, your Switch command requires a variable against which to test.


    Andy


    I think you're right. Like I said it's not something I use really, probably best to stick with the Select.

  • Re: TOC of Shared File Directory using VBA



    Boys? It looks GREAT. Code is compact, no compile errors, etc..etc.. but you know what??.. It doesn't post the information anymore. I didn't keep every revision. I wonder where we killed it?


    Some one PLEASE post it to a blank spreadsheet, (modifying the path names) and see if it works for you??

  • Re: TOC of Shared File Directory using VBA


    I'm so sorry. I apologize. Somewhere along the way, I removed the 'trigger.'


    I was hitting CTRL-a and nothing was happening. I put the 'trigger' back in, and it is working absolutely fine. Here is the code that is in the file now.



    I am absolutely AMAZED at your skills. Thank you, thank you.. thank you!

Participate now!

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