how to open worksheet by passing value into variable in VBA

Question

I'm trying to open worksheet by taking value from one cell and concate the desire file name but it shows error : object variable or with block variable not set i'm using following code : where "123.xlsx" is name of workbook that i want to open .

Dim output_path As Workbook
dim xlwkbrpt1  as workbook
output_path = Application.ActiveWorkbook.ActiveSheet.Range("F14").Value
Set xlwkbrpt1 = xlApp.Workbooks.Open(output_path & "\" & "123.xlsx")

Why is output_path a Workbook? Shouldn't it be a string? Also, print output_path & "\" & "123.xlsx" just before executing the last statement and see what the Open method is trying to open. — FDavidov
– FDavidov, Commented Nov 19, 2016 at 6:50
Please add a print of the parameter to xlApp.Workbooks.Openjust before the invocation and append the result to your question. — FDavidov
– FDavidov, Commented Nov 19, 2016 at 10:42
By the way, I see that your comment to the answer by bzimor states that the solution does nor work, but the answer is flagged as resolving the issue. So is the issue solved or not? — FDavidov
– FDavidov, Commented Nov 19, 2016 at 10:43

bzimor · Accepted Answer · 2016-11-19 06:55:09Z

1

You declared variable output_path with wrong type. Try this:

Dim output_path As String
Dim xlwkbrpt1  As Workbook
output_path = Application.ActiveWorkbook.ActiveSheet.Range("F14").Value
Set xlwkbrpt1 = Workbooks.Open(output_path & "\" & "123.xls")

answered Nov 19, 2016 at 6:55

bzimor

1,6262 gold badges16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vivek padelkar Over a year ago

I apllied your code with correction of dattype but it's still showing same error

Collectives™ on Stack Overflow

how to open worksheet by passing value into variable in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related