Opening another workbook while current one is open

[Guest]

After I have opened an existing workbook (Rates.xls"), I need to run a macro
(stored in the "Rates.xls" workbook) that will open another workbook called
"Taxes.xls" that's located in the "My Documents" folder. After "Taxes.xls"
has opened, I then need to make "Rates.xls" the active workbook so I can
continue to run the macro.
Can someone tell me how to do this, and make it so that the macro will work
on anyone's computer (the workbooks will always be located in a user's "My
Documents" folder.
Any help would be greatly appreciated. Thanks.
Bob

 

[Guest]

Maybe:-

Sub sistance_level_wages()
Set myworkbook = ActiveWorkbook
Workbooks.Open ("c:\Documents and settings\myname\my documents\taxes.xls")
myworkbook.Activate
End Sub

Mike

 

[Guest]

Mike,

Thanks for the help! Unfortunately, at the completion of your macro, the
"Taxes.xls" workbook is the active one rather than the "Rates.xls" workbook.

Bob

 

[Guest]

Bob,

If this macro is in the rates.xls workbook then it will be the
activeworkbook on completion.

Try it again with this version and when the macro stops hover over x to get
the active workbook name.

Sub sistance_level_wages()
Set myworkbook = ActiveWorkbook
Workbooks.Open ("c:\Documents and settings\myname\my documents\taxes.xls")
myworkbook.Activate
x = ActiveWorkbook.Name
Stop
End Sub

Mike

 

[john cadagin]

After I have opened an existing workbook (Rates.xls"), I need to run a macro
(stored in the "Rates.xls" workbook) that will open another workbook called
"Taxes.xls" that's located in the "My Documents" folder. After "Taxes.xls"
has opened, I then need to make "Rates.xls" the active workbook so I can
continue to run the macro.
Can someone tell me how to do this, and make it so that the macro will work
on anyone's computer (the workbooks will always be located in a user's "My
Documents" folder.
Any help would be greatly appreciated. Thanks.
Bob

Set bk = ActiveWorkbook

'this sets spath to the path to "My Documents"
fname = ActiveWorkbook.Name
pname = ActiveWorkbook.FullName
spath = Left(pname, Len(pname) - Len(fname))

Workbooks.Open Filename:=spath & "Taxes.xls"
bk.activate

 

[Guest]

Mike / John,
My apologies. I forgot that I had moved the "Rates.xls" macro over to
PERSONAL.XLS. I assume that explains why the active workbook ends up being
"Taxes.xls" (when the macro finishes running) rather than "Rates.xls".

Can you tell me how to adjust your respective macros? Sorry for the
oversight.
Bob

 

[Guest]

Bob

The macro's shouldn't require any modification. If rates.xls is the
activeworkbook when the macro is called then it doesn't matter whether the
macro is in Rates.xls or personal.xls

Mike

 

[Guest]

Mike,

Okay. But you should know that Taxes.xls (rather than Rates.xls) is still
showing up as the active workbook! I don't understand why the macro isn't
working correctly.

Bob

 

[Guest]

I don't know if it makes any difference, but Rates.xls is a delimited file.
When I first open the workbook, the Text Import Wizard dialog box pop-up and
simply click the Finish button.

 

[john]

Mike / John,
My apologies. I forgot that I had moved the "Rates.xls" macro over to
PERSONAL.XLS. I assume that explains why the active workbook ends up being
"Taxes.xls" (when the macro finishes running) rather than "Rates.xls".
change
bk.activate
to
Workbooks("rates.xls").Activate



Can you tell me how to adjust your respective macros? Sorry for the
oversight.
Bob

 

[Guest]

John,

I already tried that. But I still end up with Taxes.xls being the active
workbook. I just don't understand why it isn't working. And after the
Taxes.xls workbook opens, the macro stops!

For whatever it's worth, below is the first few lines of code in my macro
(most of which is based on what you provided me) that I execute right after I
have opened the Rates.xls workbook:

Sub CleanUp_Extract()

' This sets spath to the path of "My Documents"
fname = ActiveWorkbook.Name
pname = ActiveWorkbook.FullName
spath = Left(pname, Len(pname) - Len(fname))
Workbooks.Open Filename:=spath & "taxes.xls"

' this is where the macro inexplicably stops

Workbooks("rates.xls").Activate

' (1) Delete rows 1-5
ActiveSheet.Rows("1:5").Delete Shift:=xlUp
' rest of macro code

Bob

 

[john cadagin]

John,

I already tried that. But I still end up with Taxes.xls being the active
workbook. I just don't understand why it isn't working. And after the
Taxes.xls workbook opens, the macro stops!

For whatever it's worth, below is the first few lines of code in my macro
(most of which is based on what you provided me) that I execute right after I
have opened the Rates.xls workbook:

Sub CleanUp_Extract()

' This sets spath to the path of "My Documents"
fname = ActiveWorkbook.Name
pname = ActiveWorkbook.FullName
spath = Left(pname, Len(pname) - Len(fname))
Workbooks.Open Filename:=spath & "taxes.xls"

' this is where the macro inexplicably stops

Workbooks("rates.xls").Activate

I ran the above verbatim and it worked fine. Did you copy & paste out
of excel to your newsreader or type it here? If not pasted, there may
be a typo in excel.

Taxes.xls opens OK, and then it quits, right?

And the rates.xls file is already open before you run the macro? Does
the same thing happen when you step into the macro with f8?

 

[Guest]

John,
The way the macro is supposed to work is:
1) I open the Rates.xls file.
2) I execute the macro (which is supposed to keep Rates.xls open, and also
open Taxes.xls)
3) The macro is supposed to "return" to the Rates.xls file where the macro
then writes "lookup" formulas in various columns of Rates.xls that reference
Taxes.xls.

In case you were wondering, the reason I need Taxes.xls open at the same
time as Rates.xls is because Taxes.xls is a fairly large file. And if my
lookup formulas in Rates.xls attempt to reference Taxes.xls while it's still
closed, I get a "insufficient resources available" error message.

Bob

 

[Guest]

When I step into the macro, everything works fine! I don't understand why it
doesn't work otherwise.

 

[john cadagin]

When I step into the macro, everything works fine! I don't understand why it
doesn't work otherwise.

I wish I could help with that, but I've no clue. I've had it happen to
me on occasion, and I've never been able to figure it out. I guess
you can f8 through the first few steps and then run the rest. :/

I had one that would hang in a certain place, so I'd have to hit esc
to interrupt it and then restart it, and then it'd work. AAMOF,
looking at your last post, my problem was a large vlookup also. So you
could use my feeble method to get it to run. My guess is that it's
just a lack of memory since that's what you run into with taxes
closed.

john

 

[Guest]

John,
Yes, it's probably a memory issue. Thanks again for all your help! I
sincerely appreciated it.
Bob