VBA - Referring to This Workbook without using the File Name

PATSYS50

New Member
Joined
May 9, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi all,

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

VBA Code:
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Zyndstoff

Board Regular
Joined
Aug 17, 2021
Messages
113
Office Version
  1. 2019
Platform
  1. Windows
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?
 
Upvote 0

PATSYS50

New Member
Joined
May 9, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
2,042
Office Version
  1. 2016
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"
 
Upvote 0

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,875
Office Version
  1. 2019
  2. 2016
  3. 2010
Platform
  1. Windows
Hi,
Try this:
VBA Code:
Sheets("Sheet1").Range("AQ2").FormulaR1C1 = "=VLOOKUP(MID(RC[-42],25,19),'" & ThisWorkbook.Name & "'!Table1,2,FALSE)"
 
Upvote 0
Solution

Zyndstoff

Board Regular
Joined
Aug 17, 2021
Messages
113
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Try this:
VBA Code:
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?
 
Upvote 0

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,129
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Sheets("Sheet1") will refer to a sheet in the active workbook (just as it did in the original code).
 
Upvote 0

Forum statistics

Threads
1,186,516
Messages
5,958,300
Members
438,348
Latest member
JS050

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