+ Reply to Thread
Results 1 to 16 of 16

Pull File Names into Excel and replace dir

  1. 07-05-2011, 10:07 PM #1
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Pull File Names into Excel and replace dir

    I am still a beginner with VB. With that stated, I am creating a vb script to help make part of my job a little easier but am havign a slight issue. I need pull all the file names from a directory (including subfolders) have them output in a column in Excel 2007. I get the first part to work GREAT, BUT my main issue is that when it outputs the list I need to replace "C:\" with "\\abcdefg123\" . I need just that part replaced with the rest of the file path following. 
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 07-05-2011 at 11:47 PM. Reason: Added Code Tags
    Reply With QuoteRegister To Reply
  2. 07-05-2011, 11:57 PM #2
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    HelloSteelJ0ke,

    Welcome to the Forum!

    It would help if you posted a few more before and after examples of the desired output. Also, do you need just the subfolders of the first directory or all the subfolders (subfolders of subfolders ad nauseum).
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 07-06-2011, 07:00 AM #3
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    It would help if you posted a few more before and after examples of the desired output. Also, do you need just the subfolders of the first directory or all the subfolders (subfolders of subfolders ad nauseum).
    The current code gets me all of the subfolders and so forth, which is what i need. I just need to get the output changed.

    Example:
    current: C:\windows\boot
    need: \\downea043\windows\boot
    Reply With QuoteRegister To Reply
  4. 07-06-2011, 11:31 AM #4
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    Hello SteelJ0ke,

    Have you tried changing the path in the calling routine? 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 07-06-2011, 02:43 PM #5
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    Quote Originally Posted by Leith Ross View Post

    Have you tried changing the path in the calling routine? 
    Please Login or Register  to view this content.

    Yes I have. I'm pulling the file names from a mapped drive across the network. Currently I take the lonng road by pulling each folder in seperately and just pulling thename of the file "file.txt" and then i have the output as "\\downea043\windows\boot\" + FileItem.Name .

    But this process is sooo long specially since some of the directories go down more then 3 folders. I'm just looking to simplify the process if possible.
    Last edited by SteelJ0ke; 07-07-2011 at 10:44 AM.
    Reply With QuoteRegister To Reply
  6. 07-07-2011, 11:04 AM #6
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    I solved it!

    After playing around today I just changed my output to:

    Cells(r, 1).Formula = Replace(FileItem.Path, "C:\", "\\downea043\")

    that seems to bring me what i was looking for... Thanks for you help
    Reply With QuoteRegister To Reply
  7. 07-07-2011, 01:41 PM #7
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    ok NEW problem.. When I tested this code on my machine with Excel 2007, it worked PERFECTLY, but when i copied the code over to another laptop, also running Excel 2007 IT doen't work as well.

    It goes through all the files from the directory and outputs them. Then when it's moving down the subfolders and files, the script is starting over and writing over top. So when one subfolder finishes, it get written over by the next one.

    Is there a way to prevent this? I'm trying to figure why it worked correctly on one laptop and not another.?

    Please Login or Register  to view this content.
    Last edited by SteelJ0ke; 07-07-2011 at 01:42 PM. Reason: Edit code Tags
    Reply With QuoteRegister To Reply
  8. 07-07-2011, 01:53 PM #8
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    Hello SteelJ0ke,

    Since the code runs fine on your machine and both are running 2007, try re-coping the file. It appears the copied file is corrupted.
    Reply With QuoteRegister To Reply
  9. 07-07-2011, 01:58 PM #9
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    What I did was just do a straight copy of the text, and email it to myself so i could pull it up on my other machine. Its just a simple copy and paste of the text with changing the main dir its looking through... none of the hard coding is changed. That's why i'm confused as to why it would be acting this way.

    I know i states WHERE to start inputing the file names, and for some reason it's "restarting" for each new folder instead of just continuing with the next empty cell.
    Reply With QuoteRegister To Reply
  10. 07-07-2011, 02:35 PM #10
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    Hello SteelJ0ke,

    Well, that eliminates the possibility of a corrupted file. Do you have another machine you can try the code on?
    Reply With QuoteRegister To Reply
  11. 07-07-2011, 02:49 PM #11
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    Quote Originally Posted by Leith Ross View Post
    Well, that eliminates the possibility of a corrupted file. Do you have another machine you can try the code on?
    no i just have these to check it on.. There wouldn't be some Excel property that would make that happen is there? I tried to compare each excel program but nothing looked off.
    Reply With QuoteRegister To Reply
  12. 07-07-2011, 03:53 PM #12
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    Hello SteelJ0ke,

    There are no settings I am aware of that would cause this problem. I would try it on another machine to rule out a software problem or settings issue. At this point, I have no other ideas as to to possible causes.
    Reply With QuoteRegister To Reply
  13. 07-07-2011, 11:28 PM #13
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Unhappy Re: Pull File Names into Excel and replace dir

    I was able to try the code on a thrid machine running Excel 2007 and it behaved just as it was ment to. So somehow the work machine I need to use the code on, is messing it up.

    Is there something I can add into it so that it looks at the cell to see if theres already information in it, and if there is it goes down the column looking for the next empty cell?
    Reply With QuoteRegister To Reply
  14. 07-07-2011, 11:48 PM #14
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Pull File Names into Excel and replace dir

    Hello SteelJ0ke,

    The code finds the last cell with data in column "A" and selects the empty row below it. 
    Please Login or Register  to view this content.

    However, if there is no data or header in "A1" then it will start at "A2". Could that be the problem you are seeing?
    Last edited by Leith Ross; 07-07-2011 at 11:50 PM.
    Reply With QuoteRegister To Reply
  15. 07-08-2011, 12:54 AM #15
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Pull File Names into Excel and replace dir

    Ok, Yes I have that Line of code in there already. It must be something with this one Excel, being since I ran it on 2 other machines with the same version of excel and those ran fine.

    Thank you for the help you provided.
    Reply With QuoteRegister To Reply
  16. 07-08-2011, 10:43 AM #16
    SteelJ0ke
    SteelJ0ke is offline
    Registered User
    Join Date
    07-05-2011
    Location
    Toledo, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    10

    Cool Re: Pull File Names into Excel and replace dir

    Ok I got it to work.. For some reason I have to have the file names list in the cells before the path:

    Cells(r, 4).Formula = FileItem.Name

    I did that and it outputs just as needed.. I jut have to go and delete the name room (since i just need the path).. It's not EXACTLY how i wanted it. But atleast its giving me the file path output i need..
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1