asked on

workbooks(???).activate

I have a VBA macro which, on Excel 2000 has worked for several users for several years but since upgrading to Windows10 and Excel 2016 , one specific instruction doesn't work

My code is
…
Workbooks(DataName).activate

….

The value of the value "DataName" refers to a valid, open workbook (The other currently open workbook , from which I am trying to move active status, is "ProgName". After executing the instruction, the variable ActiveWorkBook.name remains as ProgName and I would expect it to be DataName

Norie

Does DataName include the file extension?

Does it refer to a workbook you've opened earlier in the code?

Fabrice Lambert

the variable ActiveWorkBook.name remains as ProgName and I would expect it to be DataName

Another "Active" object victim.
Set a fixed reference to your "program" workbook and your "data" workbook.
Problem solved.

In short, this is wrong:

Workbooks.open("data")
Workbooks("data").activate
ActiveWorkbook.Sheets("blabla").Range("blabla") = "whatever"

Open in new window

This is good:

Dim dataWb as Excel.workbook
Set dataWb = Workbooks.Open("data")
dataWb.Activate
dataWb.Worksheets("blabla").Range("blabla") = "whatever"

Open in new window

Roy Cox

it should work but is unnecessary. There's no need to activate a workbook

Dim dataWb As Excel.Workbook
Set dataWb = Workbooks.Open("data")
dataWb.Worksheets("blabla").Range("blabla") = "whatever"

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.