PDA

View Full Version : Force save another open workbook



Rejje
12-07-2010, 04:12 PM
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

austenr
12-07-2010, 07:10 PM
workbook("your workbook name").save

Bob Phillips
12-08-2010, 01:01 AM
Or just



wb2.Save

Rejje
12-08-2010, 06:09 AM
Or just



wb2.Save


I tried this - doesn't work. What am I doing wrong?

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

Bob Phillips
12-08-2010, 06:22 AM
You are mis-using (abusing?) variables.

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



Set wb2 = Workbook.Range("V_50200")


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



Set wb2 = Workbook.Range("V_50200").Value


because that is a type mis-match and you don't Set to non-objects.

Rejje
12-08-2010, 10:33 AM
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!

Bob Phillips
12-08-2010, 10:52 AM
Set wb2 = Workbooks.Open(Filename:=Range("V_50200"))

Rejje
12-09-2010, 04:44 AM
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:

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

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

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

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

End Sub

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!