0

I'm new to the VB coding so please help me.

I have an Excel where in the code the filename is hard coded with the file path. File name stays the same every time but the path does changes from time to time so, is there a way that we can say define the file path to look into the current directory that the calling workbook is in?

If not at worst I think we have to give the path in one of the excel columns and refer it in the code. Current code as follows:

Workbooks.Open Filename:= _ "C:\XXX\YYY\ZZZ\Called_Workbook.xls"

Any help is highly appreciated. Thanks!

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Learner
  • 13
  • 2
  • If the path is in the file to open, how do you open it to see which folder to look into for the file? vb.net, vba and excel-vba are not the same thing and have different answers so fix the tags – Ňɏssa Pøngjǣrdenlarp Nov 16 '16 at 22:38
  • Please ignore all answers except the last one from the offered duplicate post. `ThisWorkbook` is way more preferable than `ActiveWorkbook` which can be changed unexpectedly. Since the code provided by OP references and xls file I assume that OP is inquiring about VBA though I agree with @Plutonix that tags should be reviewed and edited. – nbayly Nov 16 '16 at 22:46
  • @nbayly whether to use `ThisWorkbook` or `ActiveWorkbook` or some other means of obtaining a workbook reference entirely depends on the requirements. Both are appropriate under certain circumstances. – chris neilsen Nov 16 '16 at 22:57
  • @chrisneilsen Yes, it is appropriate to use ActiveWorkbook when you want to reference the active workbook that has the focus. But when its to reference the same workbook that the code is hosted on ThisWorkbook is preferable. Even when working with other workbooks rather than use ActiveWorkbook I would favor using variables to point specifically to a Workbook. Can you detail an example where ActiveWorkbook is preferable? – nbayly Nov 16 '16 at 23:21
  • @nbayly _when should you use `ActiveWorkbook`?_ when you want to reference the active book. _when should you use `ThisWorkbook`?_ when you want to reference the book containing the code. _when should you use some other method?_ when neither of the above apply. pretty obvious really... – chris neilsen Nov 16 '16 at 23:52
  • @nbayly - Even using `ThisWorkbook` can be problematic. That's just the default code name for the object - you can name it `FooWorkbook` if you feel like it. – Comintern Nov 17 '16 at 00:29
  • @chrisneilsen Yes, pretty obvious your evident sarcasm aside. Except the one case where it's not clear... where you want to reference the active workbook that also contains the code. My only comment was in these cases where ThisWorkbook is preferable to ActiveWorkbook because the focus of the application can change while running in ways hard to predict while coding (ie. user interaction). Do you disagree? What other method do you suggest that isn't hardcoding the file when neither of these apply? – nbayly Nov 17 '16 at 15:58
  • @Comintern Ok... can you show me how to set `FooWorkbook` to the relevant workbook without hardcoding the file or using some version of a default code name for the object? – nbayly Nov 17 '16 at 16:01
  • @nbayly - Depends on what you have to work with. In the context of the linked question, `sheet.Parent` where `sheet` is a Worksheet object. If you have a `Range`, it would be `rng.Parent.Parent`. – Comintern Nov 17 '16 at 16:09

1 Answers1

2

You can extract the current file path using the method:

strFileDir = ThisWorkbook.Path

Regards,

nbayly
  • 2,167
  • 2
  • 14
  • 23