If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

Announcement

Collapse

No announcement yet.

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

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

May 16th, 2017, 15:19

Hi. I have a list of numbers in column A on the active worksheet. I need a code please that will open all the files within a folder on my desktop and tell me the name of the file each number in A is located. Thanks.

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.

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.

You have a list of numbers and you want to search all files in a folder to see which number is located in which file?

Correct

Will the required number always be in the same cell in all the files, or could it be anywhere?

Could be anywhere in the files

Can each number in the list be in only 1 of the files, or could it be in none or more than 1?

More than likely just the one file, but could be in more

How do you want the result to appear, in a message box, displayed on a sheet, if the latter what sheet and range?
In column B would be fine next to the number in A, if its in more than one file then column C and so on..

What is the name of the folder on your desktop and ,ideally, the full path to that folder.?

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.

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.

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.

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.

Comment

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

Try this. You could add a button to your sheet that has the Number List and assign the "FileSearch" macro to that button.

All these macros should be placed in the same standard module.

The workbook that has the Number List can be either put in the same folder as the files that have to be searched, or anywhere else. If in the same folder then it will be excluded from the files that get searched.

The code will search the entire used range of the first sheet without needing to open each file.

Note there are 2 places in the code where you will need to change the code to reflect the actual name of the sheet that contains the number list.

The code will search the first worksheet of all the files in the folder (except the number list file if it is in the same folder) and record the file(s), if any, that contain each number in columns B onwards for each number in the list.

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.

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.

Comment

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

If the file with the number list is kept in the same folder as the files to be searched then you can use this (the path does not need to be hard coded, so is dynamic and code will not need changing if the folder is moved or renamed).

Code:

sPath = ThisWorkbook.Path & Application.PathSeparator

If you use this method ensure that the number list file is saved to the correct folder and opened from there before testing the code.

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.

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.

Comment

Ozgrid is Not Associated With Microsoft. Ozgrid Retains the Rights to ALL Posts and Threads

All times are GMT+8. This page was generated at 1 minute ago.

Working...

X

We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, personalize advertising, and to analyze site activity. We may share certain information about our users with our advertising and analytics partners. For additional details, refer to our Privacy Policy.

By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also acknowledge that this forum may be hosted outside your country and you consent to the collection, storage, and processing of your data in the country where this forum is hosted.