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
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
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
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.