CODEWITHSUNDEEP
vbavariablesexcel
Sean Connecticut
Sean Connecticut

Reputation: 305

Using a Variable to Dim a Worksheet

I have a sheet with one cell that is = the name of a folder that I want to dim as a variable. what I want to do is set that cell = the filename variable. It will probably be easier to look at my code. I am currently getting the "object required error on my "set Input 1" and my way to set the variable is presumably wrong as well

Dim WbkA As Workbook
Dim Input1 as string

Set Input1 = Workbooks.Open(Filename:="G:\Reporting\ReportCompare.xls").worksheets("Sheet4").Range("A4").Value
Set wbkA = Workbooks.Open(FileName:"Input1")

Upvotes: 0

Views: 4851

Answers (2)

Robert Mearns
Robert Mearns

Reputation: 11996

The code needs to be reordered slightly in order to breakout the steps.

  • Get the file path and name from the workbook and store it as a string variable (Input1).

  • Open the file using the value stored in the string variable (Input1).

  • Set a reference to the open file as an object variable (WbkA).

Listed below is the code

Sub test()

Dim Input1 As String
Dim WbkA As Workbook

  Input1 = Worksheets("Sheet4").Range("A4").Value 'Get the path and file name
  Workbooks.Open Filename:=Input1  'Open the file
  Set WbkA = ActiveWorkbook 'Set the reference to the workbook

  MsgBox WbkA.Name 'Show the name value from the object.

End Sub

Upvotes: 1

Adrien Lacroix
Adrien Lacroix

Reputation: 3612

You try to assign a reference of an object with the keyword Set to a data type (String).

Remove the keyword Set and it's gonna be okay.

Upvotes: 4

Related Questions