Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    display new workbook (xl2003sp2)

    Simple question...
    I have a point & shoot macro in workbook1 in which the contents of certain cells in workbook1 are copied and pasted special values into cells in workbook2 (actually, a sort of template).
    1. How can I arrange for workbook2 to be the file visible on the screen when the copying and pasting is finished?
    2. What code must I add to achieve 1. PLUS leave a 'save as' dialogue box visible on the screen showing workbook2?
    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display new workbook (xl2003sp2)

    Something like

    Workbooks("Workbook2").Activate
    Application.Dialogs(xlDialogSaveAs).Show

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display new workbook (xl2003sp2)

    That's excellent, Hans (and I've now got the hang of it for next time....)
    The 'workbook2' appeared with the dialogue box, but minimised, but I've tracked that down to a rogue entry in the code something about
    window.state=xlminimized, which I've 'adjusted' and the adjustment worked.
    While we're on a roll, what about another couple of lines half-populating the Save As dialogue boc, telling it to save to the folder
    [drive[:/folder1/folder2/folder3
    and even better, with filename corresponding to the result of the formula
    =f6&j6
    (which I could create in a spare cell outside the print area of the 'template' if that makes it either possible, or easier) ?
    The code for the whole macro attached, in case any further simplifications possible or desirable. Like, scrubbing all those redundant?? instructions in the Paste special statements about transpose = false etc.
    Absolutely no hurry on this and keep up the good work.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display new workbook (xl2003sp2)

    If you want to set a folder and filename, its better to use the FileDialog object (introduced in Office XP).

    In general, macros run more efficiently if you don't switch between workbooks/worksheets and select cells. Below is a shortened version of your macro that only activates the target workbook near the end.

    Sub doLoanApp()
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook
    Dim wshTarget As Worksheet

    Application.ScreenUpdating = False

    Set wbkSource = Workbooks("do_it_all.xls")
    Set wshSource = wbkSource.ActiveSheet
    Set wbkTarget = Workbooks.Open("C:Credit UnionCredit Workerloanappl.xls")
    Set wshTarget = wbkTarget.ActiveSheet
    wshTarget.Range("F6") = wshSource.Range("A4")
    wshTarget.Range("J6") = wshSource.Range("A20")
    wshTarget.Range("F8") = wshSource.Range("B20")
    wshTarget.Range("F10") = wshSource.Range("C20")
    wshTarget.Range("N10") = wshSource.Range("D20")
    wshTarget.Range("F14") = wshSource.Range("E20")
    wshTarget.Range("H28") = wshSource.Range("H20")
    wshTarget.Range("H30") = wshSource.Range("H14")
    wshTarget.Range("H32") = wshSource.Range("I19")
    wshTarget.Range("F38") = wshSource.Range("C18")
    wshTarget.Range("I40") = wshSource.Range("G16")
    wshTarget.Range("F42") = wshSource.Range("I16")
    wshTarget.Range("G53") = wshSource.Range("G18")
    wshTarget.Columns("N:N").ColumnWidth = 10.89
    Workbooks("loanappl.xls").Activate

    Application.ScreenUpdating = True

    With Application.FileDialog(msoFileDialogSaveAs)
    .InitialFileName = "C:ThisThat" & _
    wshTarget.Range("F6") & wshTarget.Range("J6")
    If .Show = True Then
    ActiveWorkbook.SaveAs .SelectedItems(1)
    End If
    End With
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display new workbook (xl2003sp2)

    That's magic, Hans, and much more elegant than the code produced by the recorder!
    Definitely a worked example to keep for similar tasks in the future.
    I discovered that one of the fields copied into the target workbook contained text with loads of trailing spaces: this produced a filename like
    "467John Rose " (with the quotes"
    As I want the target workbook to be a formula-free zone, I applied the TRIM function in the source, rather than the target.
    It seems that Trim is happy to coexist with a vlookup, in a formula like
    =TRIM(VLOOKUP(balancenow!$A$4,data!$A:$L,2,FALSE))
    Thanks again.

    Last point: are you saying that this won't work before Excel XP?
    That is unwelcome!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display new workbook (xl2003sp2)

    >>I discovered that one of the fields copied into the target workbook contained text with loads of trailing spaces: this produced a filename like
    "467John Rose " (with the quotes"

    Funny, that, what I actually typed was
    "467JohnRoseand xspaces"
    The Lounge has done the trimming for me! (but not excel, regretfully!)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display new workbook (xl2003sp2)

    That is not specific to the Lounge. HTML reduces multiple spaces to a single space. If you want to preserve indents and multiple spaces, you can use the <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags:

    <!t>[pre]<!/t>
    "467 JohnRose

  8. #8
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display new workbook (xl2003sp2)

    we live and learn.....

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: display new workbook (xl2003sp2)

    Application.FileDialog won't work in Excel 2000 or before. If you want the code to operate in all versions of Excel (or at least Excel 97 and up), you have several alternatives:
    - Use the original Dialogs(xlDialogSaveAs) and forget about specifying the file name/path.
    - Use the Windows API code from <post:=505,867>post 505,867</post:> (it's in a Word document there, but it can be copied to Excel)

Posting Permissions

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