Switching between workbooks

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
177
Office Version
  1. 365
Platform
  1. Windows
I have a macro which will pull up a workbook (log file) based on your input (project number, etc.) into the macro file face.

I've figured out building the file open variable, and going back to the macro workbook (which, thankfully, is always a constant named file).

However, once you okay the entry (all built in the macro file), the macro needs to go back to the log file to enter the data. That's where my problem comes, in sending the macro back to the open file with a variable name.

Here's what I've tried:



Code:
'TRIAL #A

'Note that Macro is started from the Macro file (Document Control Assignments.xlsm)

Dim MDRProject As String      'this will contain the entire path & file of the log file
MDRProject = Range("C12")

Dim ProjFile As String
ProjFile = Range("H12")

Workbooks.Open Filename:=MDRProject

Windows("Document Control Assignments.xlsm").Activate 'this takes me back to the macro file
Call SetLogEntry

'Up to this point, everything worked fine.  Now for the problem... each error takes me to the specific line I've tried

'Trial #1 was...
Windows(ProjFile).Activate     'Doesn't work; Run-time error '9': Subscript out of range

'Trial #2 was...
Workbooks(ProjFile).Activate     'Doesn't work; Run-time error '9': Subscript out of range


Code:
'Trial B was a whole new way, after reading thru posts here...


Dim MDRProject As String
MDRProject = Range("C12")

Workbooks.Open Filename:=MDRProject

Dim ProjFile As String
ProjFile = ActiveWorkbook.Name

Windows("Document Control Assignments.xlsm").Activate 
Call SetLogEntry

'Trial #1 was...
Windows(ProjFile).Activate     'Doesn't work; Run-time error '9': Subscript out of range

'Trial #2 was...
Workbooks(ProjFile).Activate     'Doesn't work; Run-time error '9': Subscript out of range

OBVIOUSLY, I'm doing something wrong, but for the life of me, I can't figure out what (and Microsoft's new help features don't help at all to novices like me).

Help?
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
To Switch between workbooks you need to use something like this:


Code:
WkBkForCodeToRun = "WorkbookNameHere"
Windows(WkBkForCodeToRun).Activate
[COLOR=#008000]'Your Code Here[/COLOR]

This thread may be relevant to your dilemma: CLICK HERE FOR THREAD

Maybe check to see if the ActiveWorkbook.Name is actually the workbook you think it is...

Or Try "Hard Coding" It like the above example.
 
Last edited:
Upvote 0
OK, your code appears the same as mine (whereas your "WkBkForCodeToRun" is the same as "ProjFile" in mine.

I took your suggestion of hard coding the filename, and I'm still getting the same problem...

Code:
Dim MDRProject As String
MDRProject = Range("C12")

Dim ProjFile As String
ProjFile = "1631001-TS-DL-01.xlsx"

Workbooks.Open Filename:=MDRProject

Windows("Document Control Assignments.xlsm").Activate 
Call SetLogEntry

Windows(ProjFile).Activate     'Doesn't work; Run-time error '9': Subscript out of range

I get the exact same error. Argh! Am I doing something wrong with the dim statement? I know that I've got to be messing SOMETHING up, I'm just not sure what!
(PS, I am using Excel 2010)
 
Upvote 0
OK, I changed the Windows(ProjFile).Activate to Windows("1631001-TS-DL-01.xlsx").Activate, and that worked. BUT, my issue comes with that this macro will be used repeatedly to log into thousands of projects. I must have a variable for that log file.
 
Upvote 0
Try this:

Open the immediate window and type in:

Code:
?ActiveWorkbook.Name

Make sure it is what you think it is......

Then Assign a variable like:

Code:
YourVariable = ActiveWorkbook.Name

When stepping through the code with F8 do the same thing after the variable is assigned a value:

Code:
?YourVariable

Are the values what you expect?

I have used code like this before and had no issues.....seems strange its not working.
 
Last edited:
Upvote 0
Actually, it started working - quite accidentally.

I moved the Dim and = statement to just above the Workbooks.Activate statement (I usually put the Dim/= statements near the macro open). Anyway, it started working once the three lines were all together.
 
Upvote 0
Hi there,

Off to work, but just a quick comment. If I am understanding fairly well, you are using titlebar captions to activate ThisWorkbook (the wb the code is running from) and another workbook your code opens. Using captions are chancy and unneeded in this case. Rather, Set a reference to the opening workbook for better control of it, and use ThisWorkbook for the book running the code.
Code:
Dim myFile As Workbook
  
  Set myFile = Workbooks.Open(Filename:=MDRProject)
  'Windows("Document Control Assignments.xlsm").Activate 'this takes me back to the macro file
  ThisWorkbook.Activate
  Call SetLogEntry

After that, figure out what parts of SetLogEntry are depending on the workbook being active, and how to eliminate the dependency. Active/Activate/Selection/Select are rarely needed.

Mark
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top