Workbook open not working

[VBABEGINER]

The below code is not working..can any one guide..

Dim Path(1) As String
Dim Book(1) As String
Path(1) = Workbooks("ABC").Sheets("Main").Range("Z1").Value

'Z1 is combination of formula which gives path name

Book(1) = "xxx.xlsx"

Workbooks.Open Filename:=Path(1) & Book(1)

 

[Fluff]

Does the path end with a \

 

[Joe4]

It might not like parentheses in your variable names, unless you are setting up arrays (and it does not look like you are doing that).
So try removing all the ( and ) from your variable names.

 

[VBABEGINER]

Does the path end with a \

Hi Fluff, No.

 

[VBABEGINER]

It might not like parentheses in your variable names, unless you are setting up arrays (and it does not look like you are doing that).
So try removing all the ( and ) from your variable names.

actually, thing is..
Z1 =

="\\serverpath\ABC"&""&C7&""

C7 =

=B2

B2 =

=C1&"."&F1

B1 = today date
where,
C1 = =TEXT(B1,"YYYY")
F1 = =IF(OR(E1=10,E1=11,E1=12),E1,0&E1)
E1 gives me month number which I select in List..

This is the structure..

 

[Joe4]

I have no idea what your last post has to do with your original question. If there is some sort of link there, you need to explain.

What I was recommending was changing the code in your original post to something like:

Dim MyPath As String
Dim MyBook As String


MyPath = Workbooks("ABC").Sheets("Main").Range("Z1").Value


'To address potential issue Fluff brought up
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"


'Z1 is combination of formula which gives path name


MyBook = "xxx.xlsx"


Workbooks.Open Filename:=MyPath & MyBook

 

[VBABEGINER]

I have no idea what your last post has to do with your original question. If there is some sort of link there, you need to explain.

What I was recommending was changing the code in your original post to something like:

Dim MyPath As String
Dim MyBook As String


MyPath = Workbooks("ABC").Sheets("Main").Range("Z1").Value


'To address potential issue Fluff brought up
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"


'Z1 is combination of formula which gives path name


MyBook = "xxx.xlsx"


Workbooks.Open Filename:=MyPath & MyBook

Hello Joe, I really appreciate your reply..

It not works..open.

Z1 is the path name + range C7

C7 refering to another Cell i.e. B2

and, B2 contains formula...What is this formula is...B2 gives me current Year + Current Month Number...to instruct user to create a folder of this name..

before to this, I have given user a drop down list, which have months name...as soon as user month name, E1 shows month number.

Range F1 =IF(OR(E1=10,E1=11,E1=12),E1,0&E1)

The reason Im using the above formula is, User should create month folder like this - eg. 2017.month number say 09...so for 10 Oct, 11 Nov, 12 Dec should not take "zero" to before hence i have wrote this formula for user understanding..

Hope this clear's

 

[VBABEGINER]

Help appreciated please..

 

[Joe4]

Is your "ABC" workbook open in the current session of Excel? It needs to be.

Also, when I tested it out, it wanted the extension too, i.e.

MyPath = Workbooks("ABC.xlsx").Sheets("Main").Range("Z1").Value

Change the "xlsx" to match whatever extension is on this file.

 

[VBABEGINER]

Hey Joe, This works at my end...

Thanks, for providing such nice piece of information..

I have no idea what your last post has to do with your original question. If there is some sort of link there, you need to explain.

What I was recommending was changing the code in your original post to something like:

Dim MyPath As String
Dim MyBook As String


MyPath = Workbooks("ABC").Sheets("Main").Range("Z1").Value


'To address potential issue Fluff brought up
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"


'Z1 is combination of formula which gives path name


MyBook = "xxx.xlsx"


Workbooks.Open Filename:=MyPath & MyBook