Differences in the way a .xls file is opened

[John Keith]

I have a macro that does something entirely different, depending on the way I
have opened the file containing the macro.

Method 1: Launch Excel, then click the open button and select the file.
Method 2: Open my documents folder and double click the *.xls file

I am trying to select and copy row1-28, then paste this group down multiple
times based on a # in a cell of the original sheet (the one with the macro).
The copy cells are in a different workbook, and the paste ranges are also in
that same "different workbook"

When Method 1 is used, The Active sheet remains to be the 1st workbook, even
though I have code that should select and activate the other sheet.

When Method 2 is used, The active sheet switches to the "different workbook"
just like the code says to do.

Code that swaps the active sheet to the "different workbook"...
Application.Workbooks("newblankwb.xls").Activate
Sheets("Logs").Activate

This code wont seem to work for open method 1.

Why does this happen?
Is there some code that will work the same irreguardless of the way the
sheet was opened?

--
Regards,
John

[John Keith]

Additionally.. I tried
Application.Windows("newblank.xls").Activate
Application.Workbooks("newblankwb.xls").Worksheets("Logs").Select
but the results were the same
--
Regards,
John


"John Keith" wrote:

> I have a macro that does something entirely different, depending on the way I
> have opened the file containing the macro.
>
> Method 1: Launch Excel, then click the open button and select the file.
> Method 2: Open my documents folder and double click the *.xls file
>
> I am trying to select and copy row1-28, then paste this group down multiple
> times based on a # in a cell of the original sheet (the one with the macro).
> The copy cells are in a different workbook, and the paste ranges are also in
> that same "different workbook"
>
> When Method 1 is used, The Active sheet remains to be the 1st workbook, even
> though I have code that should select and activate the other sheet.
>
> When Method 2 is used, The active sheet switches to the "different workbook"
> just like the code says to do.
>
> Code that swaps the active sheet to the "different workbook"...
> Application.Workbooks("newblankwb.xls").Activate
> Sheets("Logs").Activate
>
> This code wont seem to work for open method 1.
>
> Why does this happen?
> Is there some code that will work the same irreguardless of the way the
> sheet was opened?
>
> --
> Regards,
> John

[Datasort]

John,

I am having a problem understanding the source and destination of the copy
process. What is the error you receive when you try this? Have you tried to
see if both workbooks are open in the app. It might be that when you double
click on the file name then you are launching a new instance of Excel

In the imediate window try this command ... it should return 2 or more
?workbooks.Count

Can you then see both workbooks Workbook(1).name and workbook(2).name

Regards,

Stewart Rogers

[John Keith]

A.xls contains the macro code and the cell with the number of time to repeat
the paste.

B.xls is a template with 28 rows that I want to copy into a new worksheet.

C.xls is the new worksheet to receive the 1st 28 rows copy, then will be the
source for a copy (rows 1-28) and multiple pastes into the next set of 28...

The error is that the focus is not shifting to the C.xls workbook for the
last set of copies and pastes. What happens in error is workbook A's rows
1-28 are copied then pasted on down the rows of workbook A.

I will try the .count set up once I get home in a bit and respond with the
results.
(asuming that the home PC does the same thing. I have been mixing and
matching Excel 2000, 2002 and 2003.)

It seemed to work on 2000, but I didnt really test the style of opening in
2000. It was on excel2003 that I noticed the problem. The home PC has 2002
on it. I guess this is good testing though cause I do plan on having this
macro run on all of the above... maybe excel 98 too.

Hmm come to think of it, when the focus wasnt swapping... I dont think the
newblankws.xls was open at all. (wasn't on the windows list and only 1
instance of excel was running)


--
Regards,
John


"Datasort" wrote:

> John,
>
> I am having a problem understanding the source and destination of the copy
> process. What is the error you receive when you try this? Have you tried to
> see if both workbooks are open in the app. It might be that when you double
> click on the file name then you are launching a new instance of Excel
>
> In the imediate window try this command ... it should return 2 or more
> ?workbooks.Count
>
> Can you then see both workbooks Workbook(1).name and workbook(2).name
>
> Regards,
>
> Stewart Rogers

[John Keith]

I put this code in various places in the macro... " WBS = Workbooks.Count"

Testing the value right after each assigment (with dbl-click xls file to open)

WBS starts out at 1 (only main "scenario.xls" is open)
workbook.add
WBS = 2
..saveas and call it "newblankwb.xls"
WBS still 2
..close
WBS=1
..open again...
WBS=2 and both scenario and newblank are open

Workbooks.Open Filename:="AxisLog.xls", UpdateLinks:=0

wont open... hmm
WBS=2 still. and later on I try to close the active sheet (which is
supposed to be AxisLog.xls... thats why the paste is in the wrong sheet!)

<F5> zoomed to end and the pastes were in error (only the scenario book open)

CLosed Scenario with out updating..
clicked file open and reopened it.
Put a break point after the open for AxisLog... same exact sytax and now the
Workbooks.Open Filename:="AxisLog.xls", UpdateLinks:=0
works perfectly ?!?

AxisLog.xls was and hasbeen in the same directory as the scenario.xls I am
opening. It is a template file I copy 3 sheet tabs from. it is used to
generate the first copy into the newblankwb.xls file. (so the 28 rows being
copied down has nothing to do with the real issue)

What would cause that open to not work?

(time to look up file status checking)

--
Regards,
John


"Datasort" wrote:

> John,
>
> I am having a problem understanding the source and destination of the copy
> process. What is the error you receive when you try this? Have you tried to
> see if both workbooks are open in the app. It might be that when you double
> click on the file name then you are launching a new instance of Excel
>
> In the imediate window try this command ... it should return 2 or more
> ?workbooks.Count
>
> Can you then see both workbooks Workbook(1).name and workbook(2).name
>
> Regards,
>
> Stewart Rogers

[John Keith]

The CurDir() is different depending on how you open excel.

open from dbl click file to launch excel.. (looking at C:\Documents and
Settings\MyDocuments\Sub1\Sub2 and dbl clicking a file in the sub2 directory)
X=CurDir() shows X having the value "C:\Documents and Settings\MyDocuments"

open from the "open file" button inside excel... same file ... same
directory...
X=CurDir() shows X having the value "C:\Documents and
Settings\MyDocuments\Sub1\Sub2"

Is there some setting to make these 2 different methods of opening a file
return the same CurDir() ?
Or what is the best way to make sure the entire path is used?

--
Regards,
John


"John Keith" wrote:

>
> I put this code in various places in the macro... " WBS = Workbooks.Count"
>
> Testing the value right after each assigment (with dbl-click xls file to open)
>
> WBS starts out at 1 (only main "scenario.xls" is open)
> workbook.add
> WBS = 2
> .saveas and call it "newblankwb.xls"
> WBS still 2
> .close
> WBS=1
> .open again...
> WBS=2 and both scenario and newblank are open
>
> Workbooks.Open Filename:="AxisLog.xls", UpdateLinks:=0
>
> wont open... hmm
> WBS=2 still. and later on I try to close the active sheet (which is
> supposed to be AxisLog.xls... thats why the paste is in the wrong sheet!)
>
> <F5> zoomed to end and the pastes were in error (only the scenario book open)
>
> CLosed Scenario with out updating..
> clicked file open and reopened it.
> Put a break point after the open for AxisLog... same exact sytax and now the
> Workbooks.Open Filename:="AxisLog.xls", UpdateLinks:=0
> works perfectly ?!?
>
> AxisLog.xls was and hasbeen in the same directory as the scenario.xls I am
> opening. It is a template file I copy 3 sheet tabs from. it is used to
> generate the first copy into the newblankwb.xls file. (so the 28 rows being
> copied down has nothing to do with the real issue)
>
> What would cause that open to not work?
>
> (time to look up file status checking)
>
> --
> Regards,
> John
>
>
> "Datasort" wrote:
>
> > John,
> >
> > I am having a problem understanding the source and destination of the copy
> > process. What is the error you receive when you try this? Have you tried to
> > see if both workbooks are open in the app. It might be that when you double
> > click on the file name then you are launching a new instance of Excel
> >
> > In the imediate window try this command ... it should return 2 or more
> > ?workbooks.Count
> >
> > Can you then see both workbooks Workbook(1).name and workbook(2).name
> >
> > Regards,
> >
> > Stewart Rogers