Set Name for Workbook in Macro Code

[Jluc]

So I'm sure this is an easy one but I'm not sure how to really go about it as I'm fairly new to VBA. I have a line of code in the beginning that references a file name. I would like to be able to set a name or value for this file so that when I ever run this Macro I would only have to change the target or reference to that file rather than have to change it everywhere it appears in my code. This is my failed attempt at this:

Please Login or Register  to view this content.

I'm not sure if it's my naming or referencing that's keeping this file from opening or the way I'm trying to open it.

[MarvinP]

Hi,

I use the following code:

Please Login or Register  to view this content.

which opens a cute Dialog box and waits for the user to open a file.
The I immediately capture the name of the file with a

Please Login or Register  to view this content.

Late I close the file without stopping with:

Please Login or Register  to view this content.

hopes this helps

[nimrod]

Please Login or Register  to view this content.

EXPLAINATIONS:

Workbooks.Open "wb"
This doesn't work because you've put your wb in quotes. When you put something in quotes you are telling the system that you mean litterall "wb" .... for it to be a variable you leave the quotes off.

HOWEVER... taking the quotes off is not enough... you had used "set" with your variable "wb" and when you use set you are creating an Object and NOT assigning string to a variable. Sooooo it will fail since the "Open" is expecting a string , or variable that represents a string , and instead you've passed it a Workbook Object. Sooooo


Step 1 :
Assign you string to a variable
wb = "F:\Accts\Invoice\2011\92999_3-1-2011.xls(1).xls"

Step 2
Pass String to the Open method
Workbooks.Open wb


ANOTHER EXAMPLE... (do it both ways ie. object and string var)

Please Login or Register  to view this content.