Consulting

Results 1 to 8 of 8

Thread: Force save another open workbook

  1. #1

    Force save another open workbook

    Hi!

    How does one force save another open workbook (here called wb2)?

    I'd like a macro in wb1 (where the macro is stored) to check if wb2 is an open workbook. If so: save wb2

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    [vba]workbook("your workbook name").save[/vba]
    Peace of mind is found in some of the strangest places.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Or just

    [vba]

    wb2.Save
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Quote Originally Posted by xld
    Or just

    [vba]

    wb2.Save
    [/vba]
    I tried this - doesn't work. What am I doing wrong?

    [VBA]Sub SaveFile()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Set wb1 = ActiveWorkbook
    Set wb2 = Workbook.Range("V_50200").Value ' this is where I dont get it to work

    If Range("V_10300") = True Then

    If UCase(wb1.FullName) = UCase(Range("V_50100").Value) Then
    MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " skapas!")
    ActiveWorkbook.SaveAs Range("V_50200").Value
    Workbooks.Open wb1.FullName
    wb1.Save
    wb2.Save
    Else
    wb1.Save
    End If

    Else
    MsgBox (UCase("Kan inte spara - organisationsnummer saknas!"))
    Range("EXP_1010").Select
    End If

    Set wb1 = Nothing
    Set wb2 = Nothing

    End Sub[/VBA]

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You are mis-using (abusing?) variables.

    wb2 is a workbook variable. Thus you cannot set it to a range

    [vba]

    Set wb2 = Workbook.Range("V_50200")
    [/vba]

    because that gives a type mis-match, and you certainly cannot Set it to a range value

    [vba]

    Set wb2 = Workbook.Range("V_50200").Value
    [/vba]

    because that is a type mis-match and you don't Set to non-objects.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Ok, but exact text in in the cell called V_50200 is:

    C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOSTER\KUNDPOST-5565583324.xlsm

    Cell V_50200 is also a variable. There could be a name of any workbook in it.

    Then: How do I set wb2 to understand it's a workbook with the very same name as the value in cell V_50200?

    I need the macro to save this open workbook!

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Set wb2 = Workbooks.Open(Filename:=Range("V_50200"))
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    I've discovered this is not easy. No matter how I try I can't get it to work. I have tried sooo many ways of getting this code to deliver what I want it to.

    I have tried to all suggestions so far in this string. Since none has worked properly I feel it's time to go ahead with a complete descreption of the problem.

    I have 3 cells in this workbook that I are really important for a lot of logic:


    V_50100 = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOST TEMPLATE\KUNDPOST TEMPLATE.xlsm
    This is a constant!

    V_50200 = C:\Documents and Settings\RejjeRejje\Skrivbord\Kundpost\KUNDPOSTER\KUNDPOST-5565583324.xlsm
    This is a variable until one specific cell has been filled in with a 10 digit number in the template - then it become a constant when the template has been saved as V_50200!

    V_50300 = ???
    Can say either of the above depending on if the workbook is the template or the "saved-as-10-digit-number".


    I need V_50300 to always tell what the correct name of the workbook is!

    Check this out!

    First I run:

    [vba]Sub SaveFile()
    Dim wb1 As Workbook
    Set wb1 = ActiveWorkbook

    If Range("V_10300") = True Then

    If UCase(wb1.FullName) = UCase(Range("V_50100").Value) Then
    MsgBox (UCase(Range("KUNDPOST_NAMN").Value) & " skapas!")
    ActiveWorkbook.SaveAs Range("V_50200").Value
    Workbooks.Open wb1.FullName
    wb1.Save
    Call DoubleSaveIfJustSavedAs ' TAKE NOTICE!
    Else
    wb1.Save
    End If

    Else
    MsgBox (UCase("Cannot save - 10 digit number is missing!"))
    Range("EXP_1010").Select
    End If

    Set wb1 = Nothing

    End Sub[/vba]

    [vba]Sub DoubleSaveIfJustSavedAs()
    Dim wb2 As Workbook
    Set wb2 = ActiveWorkbook
    If UCase(wb2.FullName) = UCase(Range("V_50200").Value) Then
    wb2.Save
    End If

    Set wb2 = Nothing

    End Sub[/vba]

    This is the code that deliveres the name of the workbook into cell V_50300 (located in "ThisWorkbook"):

    [vba]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Range("V_50300").Formula = ThisWorkbook.FullName

    End Sub[/vba]

    So, what the problem?

    Lets say I want to save the template for the first time. I push a button and then Sub SaveThis saves the template as V_50200. No problem - works perfectly!


    BUT! Cell V_50300 still says ".../TEMPLATE.xlsm".

    WHY??? The file name is now ".../KUNDPOST-5565583324.xlsm".

    STRANGE THING: Now if I go into the vba editor and manually run Sub DoubleSaveIfJustSavedAs then cell V_50300 says ".../KUNDPOST-5565583324.xlsm".


    What's the problem?

    It's like the macro if run in one sequence getting correct file name into cell V_50300 is impossible. When I run Sub SaveFile and Sub DoubleSaveIfJustSavedAs separately it workes perfectly.

    I would also like to add I'm very, very greatful for all the help I've been given in this forum!

Posting Permissions

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