VBA - Referring to This Workbook without using the File Name

[PATSYS50]

Hi all,

How could I make the below code work without hardcoding the filename

Sheets("Sheet1").Range("AQ2).FormulaR1C1 = "=VLOOKUP(MID(RC[-42],25,19),'Macro File.xlsm'!Table1,2,FALSE)"

The reason is to avoid error when someone changes the filename.

By the way, Sheet1 is in another workbook.

Thanks in advance.

 

[Zyndstoff]

I'ld suggest to include some vba-code in the "Macro File.xlsm" itself, that prevents it from being saved under a different name.
That doesn't help of course if the name is changed or if the file is deleted via explorer. Make it a hidden file and writeprotected to give at least some security.
I can't think of a way to reference a second file without the name - but maybe there are more knowledgeable guys here?

 

[PATSYS50]

I'ld suggest to include some vba-code in the "Macro File.xlsm" itself, that prevents it from being saved under a different name.
That doesn't help of course if the name is changed or if the file is deleted via explorer. Make it a hidden file and writeprotected to give at least some security.
I can't think of a way to reference a second file without the name - but maybe there are more knowledgeable guys here?

Hi Zyndstoff,
The codes reside in Macro File.xlsm. I am thinking maybe the expression "Thisworkbook" might do the job.

Anyway thanks for your post.

 

[bebo021999]

maybe two options:
1- Place in a specific folder with only single file. Then code should refer to that folder to get the only file, not the filename.
2- Use a specific cell in that file to store a special indentify information. for instant, Cell A1="This is my file"
Then loop through filenames to find which file with cell A1 that got "This is my file"

 

[ZVI]

Hi,
Try this:

Sheets("Sheet1").Range("AQ2").FormulaR1C1 = "=VLOOKUP(MID(RC[-42],25,19),'" & ThisWorkbook.Name & "'!Table1,2,FALSE)"

 

[Zyndstoff]

Hi,
Try this:

Sheets("Sheet1").Range("AQ2").FormulaR1C1 = "=VLOOKUP(MID(RC[-42],25,19),'" & ThisWorkbook.Name & "'!Table1,2,FALSE)"

"Sheet1" resides in different workbook. So if you call this code from the workbook where "Sheet1" is in (as I presume, since sheets("sheet1").Range("AQ2").FormulaR1C1= does not reference a specific workbook), this shouldn't work?

 

[RoryA]

Sheets("Sheet1") will refer to a sheet in the active workbook (just as it did in the original code).