0

Hi guy i am creating vba project where i need to switch from one workbook to another where i need dynamic active workbook name

so i am trying to use this code, here i am getting error "script out of range"

Dim wb
wb = ActiveWorkbook.FullName
wb = Split(wb, "\")(UBound(Split(wb, "\")))
Workbooks("wb").Activate

but when i am changing dynamic name of active workbook to static.. this code works...

Dim wb
Workbooks("project1.xlsm").Activate

please help guys.. cause i need dynamic name of active workbook...

jatin verma
  • 13
  • 2
  • Why don't you do `Dim wb As Workbook` `Set wb = ActiveWorkbook` and then just use `wb` to refer to the workbook without having to `Activate` it? – YowE3K Sep 17 '17 at 04:44
  • (But if you **have** to have the name, just use `wb = ActiveWorkbook.Name`, and then refer to the workbook using `Workbooks(wb)`.) – YowE3K Sep 17 '17 at 04:45
  • @YowE3K updated my code with Dim wb as Workbook, now error is "Object variable or With block variable not set" – jatin verma Sep 17 '17 at 04:51
  • and if i use wb=activeworkbook.name then i will get only workbook name without extension but to use "workbooks("wb") active", it should have workbook name with extension – jatin verma Sep 17 '17 at 04:53
  • You will need to show the code you are now using before we can guess as to what is wrong. (I assume the error is on one of the lines you changed to use the `wb` object - is that right?) – YowE3K Sep 17 '17 at 04:54
  • What makes you think that `ActiveWorkbook.Name` will drop off the extension? (And don't put variable names within double-quotation marks - double-quotation marks in VBA are used to signify string literals - it should be `Workbooks(wb)` if the variable `wb` is the `Name` of the workbook being referred to, and it would be `Workbooks("wb")` only if `"wb"` was the actual workbook name, e.g. `Workbooks("Book1")` is the default workbook when you first create a new workbook.) – YowE3K Sep 17 '17 at 04:59
  • @YowE3K thanks brother... it is working now... it was silly mistake that i was using double quotation on variable... – jatin verma Sep 17 '17 at 05:05

1 Answers1

1

Your error in your existing code is caused by you trying to use the String literal "wb" instead of the variable wb. So the following should work:

Dim wb As String
wb = ActiveWorkbook.FullName
wb = Split(wb, "\")(UBound(Split(wb, "\")))
Workbooks(wb).Activate

However, using the FullName and then stripping off the path component is no different to using the Name property:

Dim wb As String
wb = ActiveWorkbook.Name
Workbooks(wb).Activate

But it would be better to just create a Workbook object and use it directly:

Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Activate

Note: There are very few occasions when the use of Activate and Select is necessary. Without seeing your following code it is hard to help you with good code, but maybe it is something like:

Dim wb As Workbook
Set wb = ActiveWorkbook
wb.Worksheets("Sheet1").Range("A1").Value = wb.Worksheets("Sheet1").Range("A2").Value

It may be an idea to check out the answers to this question.

YowE3K
  • 23,852
  • 7
  • 26
  • 40