Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Differences in the way a .xls file is opened
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening an excel file differences - time | Excel Discussion (Misc queries) | |||
Close file and run macro from newly opened file | Excel Programming | |||
Is Workbook read-only because of file permission or because file is already opened? | Excel Programming | |||
How set file open path to filepath of file opened with Explorer ? | Excel Programming | |||
Opening CSV file causes date differences | Excel Programming |