Open File name contained in specific cell

[Nicolaf]

Hi,

I have some code that opens a file name contained in a specific cell (C4).

Because rows are added each day this specific cell changes daily I therefore need to make reference to cell dynamic.

"Final List" is sheet which contains cell C4 where file name is.

Code below:

Private Sub CommandButton3_Click()
    IName = ThisWorkbook.Sheets("Final List").Range("C4").Value 'name with extension
    Set NewWkbk = Workbooks.Open(Filename:="P:\Lonib\" & IName)
End Sub

I could reference Range("C4") in a way that 4 becomes dynamic.
I could for example have C stay the same but the number (in our case 4) will be the number found in Cell A1 (which will increase accordingly as rows are added).

How can I amend the code above so that Range takes C & A1 as opposed to simply C4?




Nix

[jolivanes]

Maybe name the cell (change the "C4" in the name box at the top left to i.e. "wbname") and refer to the name.
You can then add rows as you like and it will always use that value.

[Nicolaf]

Thanks can you please give me an idea of how to define wbname so that it is dymanic?

[jolivanes]

If you have the name of the workbook in cell C4, as you mentioned in post #1, you can name that cell.
If you have selected Cell C4 and look at the top of your sheet you see in the name box "C4" (without quotes)
Change that to whatever you want, you could use "wbname" or any name really. When you have changed the name in the box, hit "Enter".
When you re-select cell C4, in the name box it should have the name you gave it.
In your code you can now refer to that name instead of Range("C4")

IName = ThisWorkbook.Sheets("Final List").Range("wbname").Value

Hope that this is clear

[Nicolaf]

Great thanks that's working!



Nix