+ Reply to Thread
Results 1 to 18 of 18

Automatically register new files name into Excel ? Is it possible?

  1. #1
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Automatically register new files name into Excel ? Is it possible?

    Hello guys.

    I dont know does this question make sense or not. But i think its free to ask. So here it is.


    Everyday i will scan hundreds reports to pdf into one specific folder, and rename them in typical format of their types,serial number and operation number.
    Example , Dimensionreport#sn875577457#opn8700

    Then i have to update those serial number and operation number into spreadsheet manually. This process will be really time consuming.So my question is, is it possible to have any macro or formula that can automatically update every new pdf files details ( serial & opn number) into spreadsheet ? Can we do that way or any better options?


    Thanks in advance for helping.

  2. #2
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Automatically register new files name into Excel ? Is it possible?

    untested mock code. Modify to fit your need

    Please Login or Register  to view this content.
    Regards,
    Vandan

  3. #3
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    ohhh thanks dude...So this great thing does exist huh..

    To be frank, i'm not a 'coding' guy,so not entirely familiar with vba. I really want to work this out, but not today on my precious weekend. So i will try this on Monday using office computer.

    Anyway I'm glad for your hint, and i hope u can guide me through all modifications that i am stuck at later. Just to clear things out, i would like to ask

    1) So my current renaming style is like #dimension#sn176666388#opn0400. ,So under this one pdf filename , Can it be transfered to excel and divided into three column which is Report type(can be dimension, welding) , Serial num (sn) , and operation number (opn)?
    2) Does this code, will work automatically for every new files entry. Or do i have to run this code and the filename registration will be updated then ?

    Really appreciate your help.

  4. #4
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    i have tried this code, but seems i'm having difficulty to modify it to suit my specs. It cant be run, maybe on reason the directory that i use "C:"
    is not allowed by vba.


    Told u, i know nothing about vba.So, what point that i miss?



    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Automatically register new files name into Excel ? Is it possible?

    Change sFileName = Dir(C:Users \ roronoa \ Documents & "\*.pdf*")
    to
    sFileName = Dir("C:\Users\roronoa\Documents" & "\*.pdf*")

    Also to answer your questions from #3:
    1. Once you import file name, you can use excel function to extract needed info
    2. This code - once fixed up - will add new file to its list without duplicating

  6. #6
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    Maybe try this:

    Please Login or Register  to view this content.
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  7. #7
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    Quote Originally Posted by vandan_tanna View Post
    Change sFileName = Dir(C:Users \ roronoa \ Documents & "\*.pdf*")
    to
    sFileName = Dir("C:\Users\roronoa\Documents" & "\*.pdf*")

    Also to answer your questions from #3:
    1. Once you import file name, you can use excel function to extract needed info
    2. This code - once fixed up - will add new file to its list without duplicating

    I tried to put 4 pdf files in one folder, and set the the directory into code. i would say it partially works, because it only capture the name one file out of 4.. How can i fix this ?

    ---------- Post added at 03:23 AM ---------- Previous post was at 03:20 AM ----------

    Quote Originally Posted by abousetta View Post
    Maybe try this:

    Please Login or Register  to view this content.
    where should i insert the directory or anywhere else that i should consider to modify? Can u red highlight them anyway. Cause i'm not so familiar with vba sir.

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    If you want to hard code other file paths then just change the value of fpath.

    For example;

    Please Login or Register  to view this content.
    Or if you want you can add a folder picker at the beginning of the code to bring up a dialog box asking you to choose the folder you want to use

  9. #9
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    Totally work now. Thanks for helping.

    Just one additional question. Can i insert two path into this code, which means the files names will be extracted from two different ?


    Or otherwise i would just sign macro for every separate folder respectively.

  10. #10
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    Hi,

    I haven't forgotten about your last question. You will need to create an array and loop through the elements of the array. I have modified the example above to show you how:

    Please Login or Register  to view this content.
    This example loops through both the files on the desktop and my documents. You can of course change this to serve your own needs.

  11. #11
    Registered User
    Join Date
    08-07-2012
    Location
    United states
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Automatically register new files name into Excel ? Is it possible?

    Sorry wasnt aware of that
    Last edited by dtaganovic16; 08-14-2012 at 02:30 PM.

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

    In your new thread, please upload a dummy copy of the current structure you are using for the individual files and the main files that the individual files are being imported to.

  13. #13
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    Dear Abousetta,

    Sorry for one another late additional question. But me myself not sure it should belong here or not.

    Currently i'm using your code, and its doing just fine.But the problem is the nature of my folders itself, where the pdf files inside age about 3 years and more. When i combine 5 folders into your code array,they total up to 50 thousands m dragging the whole process run for about 30 minutes. So thats quite time consuming.

    Is there any way i want the code to capture the filesname with the latest modified date by one month ago only? Lets just specify one date maybe as reference. Because i cant afford to delete or housekeeping the files in my folder in order to reduce them.

    Hope you will consider my late request. Thanks for helping.

  14. #14
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    There is a faster way using the scripting dictionary. I should be able to code and test later this evening. The 50,000 files might get run in a matter of seconds to a few minutes using this process. Let me get back to you on that.

    As for using the date, I think there is a way to check the date but it might be slower because it will still have to check the date of each file it comes across in the five folders. And at the same time I honestly haven't ever coded this before so I will have to do some digging around to see how to read the file properties.

  15. #15
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    if u are saying so, then i'm totally fine. You know better.

  16. #16
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    Here is the updated code:

    Please Login or Register  to view this content.
    To test this I created 50,000 dummy pdfs (took about 4 sec) then I created a list of all the names using the above code (under 13 sec). So I would probably say this approach is wee bit faster than using the find method. I do also have to give credit to writing all the data to the Excel sheet only once at the end as opposed to printing it line by line.

    Let me what you think.

    abousetta
    Last edited by abousetta; 08-08-2012 at 11:27 PM.

  17. #17
    Forum Contributor
    Join Date
    07-04-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    105

    Re: Automatically register new files name into Excel ? Is it possible?

    i just got the oppurtinity to try ur code..and yeah, its better and quick enough.

    Thank you sir.

  18. #18
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Automatically register new files name into Excel ? Is it possible?

    Glad it all worked out.

    Good luck.

    abousetta

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1