Find file in the folder. Name of the file equal to cell text value

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

crosssposted: mrexcel.com/board/threads/find…e-in-text-format.1155155/

Hello everyone,

I’m layperson in VBA coding. Would be grateful for any help.

I have a code as it follows below.

It has list of stock symbols (1 to 6 letters each) in the column K

It loops through each cell in column K and returns value to cell B4

Then it calls macro GetYahooData to download historical price data (of the stock symbol in cell B4) into the range of cells and copies these data.

I want to modify code so that it could finds CSV file in the folder C:\VBA and open it.

The file name corresponds to current stock symbol in Worksheets("Sheet2").Range("B4").Value

So if for example current value of cell B4 is AAPL the file name will be AAPL.csv

It is important that if stock symbol is just A it should not open every file containing A in its name.

The aim is to paste copied historical price data of each stock into corresponding file.

The rest of the code modification I hope I will manage myself.

Hello alansidman and royUK,

My apologies for breaching forum rules. Rules have been read and understood. I did not expect that ozgrid.com and mrexcel.com are related websites. Sometimes posts remain without response for a long enough time, sometimes some solutions are better than other ones. I thought there would not be harm to post request for help somewhere else. My understanding, as a result of warning from moderators of both websites, none of my posts have been attended so far. Kind regards.

Dilshod

Hello Roy,

Please find an example of workbook as an attachment. Thank you.

MasterFile.xlsm

Dilshod

Quote from royUK

We don't ask you not to crosspost, simply post links to all other forums that you have asked in.

Got it. Will do that.

Quote from royUK

What's not working for you? It looks like the code is commercial from an addin.

Thanks for your message. I've downloaded this file couple of years ago while it was free. It's an old version. Even after it became commercial authors of the code mentioned on their website that they have no objections against VBA modifications. I'm trying to modify it to my needs. The aim is to create back testing simulator. I've bunch of CSV files with 3 years historical price data of the stocks. The historical price data for the last several days are missing. In order to avoid downloading all data from scratch for the last three years again I want to download data for the last few days and copy and paste data in the corresponding files in order to update them. Their workbook is not expensive and I would by it if it would help me. I believe this function is not available even in commercial version of the file. Thanks in advance for your help.

for 7500+ stocks it takes around 10 hours

Quote from royUK

So ou just want to import the existing data from the csv files? Sounds like it will be a massive amount of data.

Thanks for your message.

Sorry for my English, it is not my native language. Apparently I could not explain properly. It is the other way round, I want to pull data to CSV files. Let's say the folder with CSV files has historical price data starting from 01/01/2017 up to 24/11/2020. Today is 06/12/2020. I need to download stock prices for the last few days starting from 25/12/2020 up to date, copy and paste these data in each corresponding file in the folder with CSV files. For example if I have stock symbols ABC, LMN, XYZ in the column K, I need to download price data for all these symbols and then paste this data into each corresponding file namely ABC.csv, LMN.csv, XYZ.csv in the folder C:\VBA. I've composed macro to loop through all cells in the column K and download prices but I got stuck as I do not know how to find corresponding to stock symbol CSV file in the folder C:\VBA and open it. If you have any questions please let me know. Thanks in advance for your help.

Hello everyone.

I would like to simplify task as my explanation of the whole task might be confusing.

I have a folder C:\VBA with number of CSV files. Names of the files might be 1 to 6 letters.

For example: A.csv, MN.csv, XYZ.csv, OPRS.csv, TVIXF.csv etc.

I need piece of code which finds file, the name of which is matching current value of the cell B4, Sheet2, This workbook in text format.

For example, If cell B4 value is XYZ the code has to find and open file XYZ.csv in C:\VBA

Thanks in advance. I would be grateful for any help.

Just to inform that I found solution for this problem. For the time being help is not required. Thank you.

Hello everyone.

Just to inform that I found solution for this problem.

For the time being help is not required.

Thanks everyone for your help.

Dilshod

Hello Alan,

Sorry, I did not expect that solution found personally by the author of the post might be interesting to others.

Yesterday was day off, apparently due to this reason my post remained unattended on both websites.

I did homework and succeeded to find solution myself. The code is as it follows below.

Kind regards,

Dilshod