PDA

View Full Version : [SOLVED] VBA to assign & then access a workbook a variable



simora
10-19-2016, 03:01 AM
I'm trying to assign a workbook a variable which is the value of a cell.
When I try to access that variable, I get an error.
Why does this not compute as intended.

The value in Sheets("Sheet1").Range("A5") is = book2.xls saved as text



Sub wobkGet()
Dim FTW As Long
Dim wb2
Set wb2 = ThisWorkbook.Sheets("Sheet1").Range("A5")
FTW = Workbooks(wb2).Workbooks("Sheet1").Cells(3, "E").Value ' Type Mismatch
MsgBox FTW

End Sub

mana
10-19-2016, 04:03 AM
Option Explicit

Sub wobkGet()
Dim FTW As Long
Dim wb2 As Workbook

Set wb2 = Workbooks.Open(ThisWorkbook.Sheets("Sheet1").Range("A5").Value)
FTW = wb2.Sheets("Sheet1").Cells(3, "E").Value
MsgBox FTW

End Sub

simora
10-19-2016, 04:14 AM
Thanks mana:

Because I'm selecting a workbook that is already open, ( The most likely senario), it will keep giving me an error.
I'm just trying to Set wb2 = ThisWorkbook.Sheets("Sheet1").Range("A5").Value

mana
10-19-2016, 04:19 AM
Option Explicit

Sub wobkGet()
Dim FTW As Long
Dim wb2 As String

wb2 = ThisWorkbook.Sheets("Sheet1").Range("A5").Value
FTW = Workbooks(wb2).Worksheets("Sheet1").Cells(3, "E").Value
MsgBox FTW

End Sub

snb
10-19-2016, 04:39 AM
Sub M_snb()
msgbox Workbooks(ThisWorkbook.Sheets("Sheet1").cells(5,1)).sheets("Sheet1").Cells(3, 5).Value
End Sub

simora
10-19-2016, 04:45 AM
Thanks mana:

That code works as expected. Appreciate it.

simora
10-19-2016, 04:51 AM
Hi snb:

Thanks.
Not sure why but that code gave me an error.
I was looking for a solution that let me use Workbooks(wb2). in the format like I was trying.

snb
10-19-2016, 06:12 AM
Avoid variables that do not vary:


Sub M_snb()
msgbox Workbooks(ThisWorkbook.Sheets("Sheet1").cells(5,1).value).sheets("Sheet1").Cells(3, 5).Value
End Sub

And prefer With ... End With to object variables.