Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    I have some trivial code to switch to another window, copy, return to the original window and paste. No problem if the filenames are always the same...

    However, as the code is in a template the first time I open it the filename ends ...1.xls then the next time it ends ...2.xls and the code chokes at:

    Windows("filename1").Activate

    How do I reference the current file name in this context? Do I need a variable to hold the file name and pass that to the activate statement?

    NB the file is not saved at this point but I can add a file save if necessary.

    stuck (again, still at the bottom of the VBA learning curve)

  2. #2
    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: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    Add at the start of the macro
    <pre>Dim OrigWindow as string
    OrigWindow = activewindow.Caption
    </pre>

    At the end:
    <pre>Windows(OrigWindow).activate
    </pre>


    steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    Hard to know without seeing the relevant code, but is the code structured such that you can use "ThisWorkbook" or "ActiveWorkbook" to address the appropriate WB in place of "WorkBook("Name1.xls")? Alternatively, can you mess with Activating the WB then getting "ThisWorkbook.Name" to address the specific file?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    I guess the answer to your question is to never leave! When you open/create another workbook, save a reference to it, then you can do things directly. It is very rarely necessary to do a Select or an Activate and it wastes a bunch of time. Look at these two routines. The first one is answering your question with your mindset, but the second is like what you really want to do. -HYUS (hope you're un-stuck) Sam
    <pre>Option Explicit
    '
    Sub directAnswer()
    Dim winSave As Window
    Dim w As Workbook
    Set winSave = ActiveWindow
    Set w = Workbooks.Add
    MsgBox "Do some work here"
    winSave.Activate
    End Sub
    '
    Sub doThisInstead()
    Dim wsNew As Worksheet, wsCurrent As Worksheet
    Set wsCurrent = ActiveSheet
    Set wsNew = Workbooks.Add.Sheets(1)
    wsCurrent.Range("B8:C12").Copy wsNew.Range("B13")
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    ********************
    with your mindset
    ********************

    Don't like the sound of being stuck <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/groan.gif border=0 alt=groan width=16 height=15> in a rut so I'll look into your second option.

    Thanks,

    (possibly un)stuck

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    I didn't mean for that to be sound critical. As we are learning, we record macros to see how Excel does it, but unfortunately, Excel records all of those selects and activates which are totally unnecessary. For example to put "Hi" in cell A3, Excel records
    <pre> Range("A3").Select
    ActiveCell.FormulaR1C1 = "Hi"
    Range("A4").Select</pre>

    but all that is needful is
    <pre> Range("A3") = "Hi"</pre>

    or better
    <pre> Worksheets("Sheet1").Range("A3") = "Hi"</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    I didn't take it as a criticism. You indicated what I suspected, there is a more elegant solution to my problem that I couldn't see because of inexperience.

    Keep the advice coming! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    stuck

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    OK, but I'd rather be "composing" with a Beatbug. Why don't they sell the things? If they made a hybrid wireless optical mouse/Beatbug, just think of the possibilities!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    This is the trick I was after:
    <font face="Georgia">wsCurrent.Range("B8:C12").Copy wsNew.Range("B13")</font face=georgia>

    In my context it has become:
    <font face="Georgia">wsSource.UsedRange.Copy
    wsCurrent.Range("A1").PasteSpecial Paste:=xlValues</font face=georgia>

    All I need now is the correct syntax for:
    <font face="Georgia">Set wsSource = always_the_same_ filename.xls</font face=georgia>

    Or, as the source file always has the same name, can it be hard wired prior to the <font face="Georgia">.UsedRange</font face=georgia>?

    stuck

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    If you open the source with VBA, then after the Open
    <pre> Set wbSource = ActiveWorkbook</pre>

    otherwise
    <pre> Set wbSource = Workbooks("myName.xls")</pre>

    Notice that I changed the ws to wb. When referencing a range, you should fully qualify it: myBook.mySheet.myRange, otherwise, Excel will try to read your mind and it often does this incorrectly. In your case, I think that the lines you want are:
    <pre>Dim wsSource As Worksheet, wsCurrent As Worksheet
    Set wsCurrent = ActiveSheet
    Set wsSource = Workbooks("myName.xls").Worksheets("Sheet1")
    wsSource.UsedRange.Copy
    wsCurrent.PasteSpecial Paste:=xlValues</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    Yes! <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>.

    I am encouraged by the fact I was nearly there. In my many attempts I had tried
    <font face="Georgia">Set wbSource = Workbooks("myName.xls")
    wsSource.UsedRange.Copy</font face=georgia>

    to no avail and I think I tried the Set statement with .Worksheets("Sheet1") on the end but in my Dim statement I had said, wsSource As Workbook , because it is a separate Workbook. Now I see what you mean about spelling it out completely. My source file *is* a worksheet but I must tell Excel which Workbook it is part of.

    Another rung up the ladder.

    (un)stuck

  12. #12
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning to where I came from (XL97 Sr2 on Win 2000/NT4)

    Sam,

    I reckon they don't sell them because if the mouse doubled as a Beatbug no one would ever get any work done!

    Other readers see / further comments to <post#=180453>post 180453</post#> please.

    stuck

Posting Permissions

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