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

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
Hello everyone,
I’m layperson in VBA coding. Would be grateful for any help.
I have a code as it follows below.

VBA Code:
Sub GetPrice()

Dim ws As Worksheet

Dim cell As Range

For Each cell In Range("K1:K100")

If cell.Value <> "" Then

Worksheets("Sheet2").Range("B4").Value = cell.Value

Call GetYahooDataFromJSON

End If

Next cell

End Sub


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 find 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.

Also asked here Find file in the folder. Name of the file equal to cell text value - OzGrid Free Excel/VBA Help Forum
 
Last edited by a moderator:

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
  • 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.
 

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
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
 

dilshod_k

Board Regular
Joined
Feb 13, 2018
Messages
76
Solution of the post as it follows below:

VBA Code:
Sub OpenFile()

Dim myfile As String
Dim directory As String
Dim FileExt As String

directory = “C:\VBA\”
FileExt = “.csv”

‘Cell B4 = value = stock symbol

myfile = directory & Cells(4, 2).Value & FileExt

Application.Workbooks.Open Filename:=myfile

End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,605
Members
414,393
Latest member
Vignesh Mechz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top