Code To Open Files And Tell Me The Name Of File Where Data Is Located.

  • Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.


    Quote from KjBox;792609

    I also ran it on the files you sent and all was OK.


    Yes that could well be the reason for the repeated file names. I did not take multiple entries of the same number in any single sheet into account as I thought it would not happen. Do you want me to amend the code to check for multiple instances of the number in a sheet and then stop the file name being repeated?


    Only if it's an easy fix, don't worry otherwise

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!


    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.


    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  • Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.


    Not difficult at all, just needed another If.....End If statement in order to stop repeating file name if the Supplier Code occurs more than once on a sheet.


    Try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.


    Quote from KjBox;792624

    Not difficult at all, just needed another If.....End If statement in order to stop repeating file name if the Supplier Code occurs more than once on a sheet.


    Try this


    Thanks for all your time and effort, works perfect.

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!


    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.


    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  • Re: Code To Open Files And Tell Me The Name Of File Where Data Is Located.


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Sorry to revisit this after a year! I have gone to use it but its not listing where all the numbers in 'A' are found? I know they are in the files. I've noticed in the code it says A1:AM5000, does that mean it is searching only in the first 5000 rows? If so some files have as many as 200000 rows. Thanks.


    Also some files are .xlsm files with before close codes, so this code needs to ignore these codes and just shut the files down without doing anything if its applicable.

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!


    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.


    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  • Have you had chance to look at this yet please KjBox?

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!


    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.


    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  • Yes, data is checked and retrieved from the first 5,000 rows. I understood the maximum would be 2,000 rows of data.


    If there can be up to 200,000 rows then change the code to A1:AM300000 . That will allow for up to 300,000 rows of data (make it larger if you think there could ever be more than 300,000 rows of data.


    Since the code uses an ADO connection and SQL the data files never actually get opened, the data is extracted from closed workbooks. That means any WorkBook_Close code will never fire.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox I need a code where it opens all files within a folder similar to this but inserts another column with a header in all files. Is this possible please?

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


    Always try any codes provided on a copy of your workbook. Once a macro is run it cannot be undone!


    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.


    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

  • The code I provided uses SQL, that can be used to retrieve data from a closed workbook without needing to open the workbook. It is not possible to write data to a closed file using SQL.


    Since this is a new topic please start a new thread to ask it.


    Thanks.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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