Switching between workbooks

[melodramatic]

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:

'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

'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?

 

[mrmmickle1]

To Switch between workbooks you need to use something like this:

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.

 

[melodramatic]

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...

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)

 

[melodramatic]

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.

 

[mrmmickle1]

Try this:

Open the immediate window and type in:

?ActiveWorkbook.Name

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

Then Assign a variable like:

YourVariable = ActiveWorkbook.Name

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

?YourVariable

Are the values what you expect?

I have used code like this before and had no issues.....seems strange its not working.

 

[melodramatic]

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.

 

[GTO]

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.

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