Excel VBA help needed

[mingrong9757]

Hi guys I need help on how to open a excel file without the full file name.
For example I need to open the file named "EACP_VI MAY 2017.xls".
How do I go about opening the file without the full file name if it's to be opened on a URL e.g.
workbooks.open filename "http://www.sharewithme.com/EACP_VI%20MAY%202017.xls"

Because the file name changes every month and I can't possibly change the code every time e.g.
MAY --> "EACP_VI MAY 2017.xls".
JUNE --> "EACP_VI JUNE 2017.xls".
2018 JUNE --> "EACP_VI JUNE 2018.xls"

Many helps thanks. Really appreciate it.

 

[peterchan75]

You can store your file name in a spreadsheet e.g. Column A in Sheet1. I assume that your Excel files are in the same folder as your Excel macro file. If will loop through your file list and check if file exit. If yes, open it. Do whenever you need to do with it and close the file.

my_path = Thisworkbook.path
my_path = my_path & "\"
For i = 1 to Thisworkbook.Sheets("Sheet1").Cells(.Rows.Count,1).End(xlUp)Row
     if Dir(my_path & Thisworkbook.Sheets("Sheet1").Cells(i,1)) <> "" then
        Workbook.Open my_path & ThisWorkbook.Sheets("Sheet1").Cells(i,1)
        'do whatever to the files
       Workbooks(Thisworkbook.Sheets("Sheet1").Cells(i,1)).Close
     endif
next i

 

[mingrong9757]

Are there any other possible ways because I can't really type the file name in one of the excel spreadsheet itself as the file name changes every month. It is just that the initial few characters of file name is the same. Or perhaps I don't really understand your code because I am still a excel vba newbie.

I have a command button with all the data contents and once it is 'clicked' I need to open the required file and populate data inside the opened file.
The population of all data works correctly. It is just that I have problems opening the required file.

 

[peterchan75]

Your macro will not know the file name unless you indicate somewhere.
You must at least input the unique part.

E.g. June in cell B1
="EACP_VI "&B1&" 2017.xls" in cell A1

Then you just need to key in the month in Column B and copy & paste the string formula in Column A.

 

[u0206397]

https://www.techonthenet.com/excel/formulas/monthname.php

 

[desmond_2003]

MsgBox "EACP_VI " & UCase(Format(Date, "MMM")) & " " & Format(Date, "yyyy") & ".xls"