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
Printable View
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
[vba]workbook("your workbook name").save[/vba]
Or just
[vba]
wb2.Save
[/vba]
I tried this - doesn't work. What am I doing wrong?Quote:
Originally Posted by xld
[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]
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.
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!
[vba]
Set wb2 = Workbooks.Open(Filename:=Range("V_50200"))
[/vba]
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!