Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Active Window (2003)

    I have recorded the attached code which copies data from the source documents into the destination worksheet(s) within a compiled workbook. This works ok if there are no other documents open other than the destination workbook when the code is executed. I want to strengthen the 'Active Window' usage to avoid this going belly up in the event of other worksheets being open. Help please.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Thankyou Steve, but I am getting errors when setting the source workbooks.
    ..............................
    (beware "air code")
    .............................. Sorry, I don't understand what you mean?

    As your code is'nt closing the source workbooks, am I correct in thinking that they are not being physically opened?

    Thanks
    Attached Images Attached Images

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    Sorry the line should be (I edited it in the file):
    Set wkbNew = Workbooks.Open<font color=red>(</font color=red>Filename:=strpath & "DAILY REJECTIONS SUMMARY.XLS"<font color=red>)</font color=red>

    To close the workbook without saving:
    wkbNew.Close(false)

    "Air Code" means I did not test it at all. I replied to your email, creating the code then and there (which is why it is not formatted). It may contain some minor editing errors or (as the line shows) syntax errors. The intent is to demo how it could be done, but is not complete...

    Note also your strPath variable must also be defined. Your code did that, I only added the features to copy from the new workbook into the workbool that was active before the new workbook was opened...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 31-May-08 07:32. Corrected syntax error)</P>How about something like this (beware "air code")

    Dim wkbOri as workbook
    Dim wkbNew as workbook
    set wkbori = activeworkbook
    set wkbnew = Workbooks.Open (Filename:=strPath & "DAILY REJECTIONS SUMMARY.XLS")
    wkbnew.Cells.Copy
    wkbori.Sheets("BC47101").Range("A1").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False


    Steve

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Thankyou Steve, I have sorted both the workbook open and close, which is now good. Though I am now getting an error at:

    wbkNew.cells.copy

    I can't see why this is a problem, copying the entire cells of the active worksheet in the previously opened workbook??
    Am I missing something here? Your help is very much appreciated!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    Another brainfart, I forgot to include the worksheet to copy....

    Try
    wbkNew.<font color=red>Worksheets(1).</font color=red>cells.copy

    to copy the first worksheet, or if you want to copy a particular one (eg one named "Mysheetname")
    wkbnew.worksheets("MySheetname").cells.copy

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Thanks Steve.
    I figured that it was to do with the worksheet....
    I did not know that <font color=red>Worksheets(1)</font color=red> would work. I will use that!
    I had tried <font color=red>Worksheets"MySheetname"</font color=red>, but failed to use the brackets. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    We all make those little mistakes. Notice that I made several syntax errors in my post...

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Ok, I'm up and running and all is looking good. Except for the line <<< Range("A1").Select >>>, which is failing to execute. I have tried
    <<< wkbOri.Sheets("BC47101").Range("A1").Select >>>, but get an error. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Attached Images Attached Images

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Apologies Steve, but I have run into another error when trying to open a xltext file. I have recorded a macro to do this which executes fine, but I then need to change the 'recorded filepath' to strPath = ThisWorkbook.Path. I have:

    Set wkbOri = Workbooks.OpenText <font color=red>(</font color=red> Filename:=strPath & "test open text.xls" _
    , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 1)), TrailingMinusNumbers:=True<font color=red>)</font color=red>

    I am not sure that my brackets are correct (I have tried moving them about), but I am getting a compile error as shown below.

    Note to self: must work on 'debugging skills' (once mastered userforms <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>)

    Thanks for all your help!!
    Attached Images Attached Images

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    Why do you think it is not executing?
    If I presume that you want to select cell A1 of the BC47101 worksheet in the activebook when the code was started (wkbOri), then you are just mistaken about what the code does.

    What the line: Range("A1").select does is to select cell A1 of the activesheet in the activebook. When the code is run, the activebook is wkbNew since it becomes active when the file is open. It is unclear what the activesheet is in the that book since it is never set or checked. It will depend on what the activesheet was when that workbook was saved and could vary each time the code is run.

    The line: wkbOri.Sheets("BC47101").Range("A1").Select will get an error for 3 things off the top of my head. The first 2:
    if there is no sheet named "BC47101"
    or
    if it is not a worksheet (eg is a chart sheet which has no cells) is not a sheet

    Are most likely not true since if either were true you should get an error when the pastespecial line was run, not the selecting line. The other reason it could cause an error is if the wkbori was not the activeworkbook and the activesheet in wkbOri were not "BC47101".

    From you code wkbOri should be the activeworkbook, but I see nothing that requires BC47101 to be the activeworksheet...
    The best way to go to a particular workbook and worksheet is:
    Application.Goto wkbori.Sheets("BC47101").Range("A1")

    This will ensure that wkbori becomes the activebook, and that BC47101 is the activesheet...

    Steve

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    I think the problem is that you are opening the file as a TEXT file (by usinig "open Text" it is not a workbook)

    Try:
    Workbooks.OpenText Filename:=strPath & "test open text.xls" _
    , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
    Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
    Array(9, 1)), TrailingMinusNumbers:=True
    set wkbOri = activeworkbook

    [Do you really have a textfile that has an XLS extension?]

    Steve

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Thankyou Steve.

    I presumed that the active book would be the book just pasted into. Your explanation clarifies that that is not the case (though I can't quite get the logic to it!). Therefore, i have set up as follows:

    Set wkbNew = Workbooks.Open(Filename:=strPath & "Daily rejections Summary.XLS")
    wkbNew.Worksheets(1).Cells.Copy
    wkbOri.Sheets("BC47101").Range("A1").PasteSpecial _
    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    'At this point, the active book is wkbNew (the source file) (although wkbOri was last used????)

    wkbNew.Close (False)

    'Now that wkbNew has been closed, wkbOri (the destination file) is the active book and BC47101 is the active sheet (as was last used)

    wkbOri.Sheets("BC47101").Range("A1").Select


    I think I understand your explanation, my brain is just a little frazzled!!

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Active Window (2003)

    Steve,

    It is an excel file, though it was generated as a result of bat scripting that compiles 4 text files into one xls. When opening the xls file, you are faced with the Text Import Wizard.

    Hope that makes sense.

    I think it's all working now, many thanks for all your help!

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Active Window (2003)

    It is not the activeworkbook since nothing was done to activate the workbook...

    The line
    Set wkbNew = Workbooks.Open(Filename:=strPath & "Daily rejections Summary.XLS")

    Opens a new workbook with the name given. Opening the workbook activates it making it the activeworkbook. The worksheet that was selected when it was last saved will be activesheet and the activecell will be the cell that was active when it was saved

    wkbNew.Worksheets(1).Cells.Copy

    Copies all the cells in the first worksheet. The first worksheet may or may not be the activeworkbook. It does not matter since the sheet is not being selected in any way.

    This line:
    wkbOri.Sheets("BC47101").Range("A1").PasteSpecial _

    Pastes to that range. It is not selected, nor activated, so it is not the activebook and does not have to be on the activesheet or even the activecell. Copying and pasting can be done (and is faster) if the activecell, sheet or book is not changed, so the code is written to minimize selecting and activating unneccessarily.

    The code just says to pastespecial, it does give XL any reason to activate the book or the sheet.

    Hope that is a little more clear.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •