If file contains certain text, how can I extract a string from the file and input it into a cell? (VBA)
- by Kristina Kotonika
Say I have the following path and file name:
P:\Actuary\Cash Flow Forecast\Annual and Quarterly Budget Data\ECMQA 2012Q1.xls
I want to write an if then statement that does the following (not sure if my statement is set up properly):
If InStr(1, "P:\Actuary\Cash Flow Forecast\Annual and Quarterly Budget Data\ECMQA 2012Q1.xls", "QA", vbTextCompare) > 0 Then
BD.Sheets("Sheet1").Range("C2").value = "2012Q1"
End If
Instead of just inputting "2012Q1", I want it to automatically read this from the file. The thing is I am actually looping through 12 or so files and there's two types, "ECMQA 2012Q1.xls" (or ECMQB 2012Q2.xls and so on) AND "ECM Annual Budget 2012.xlsx"
If my file is the annual one (If file contains "Annual"), then I want:
BD.Sheets("Sheet1").Range("C2").value = "2012"
And i want it to read this from the actual file, same as the other one...not me putting in "2012"
Is there a way to do this?
Any help will be appreciated!
