Re: Save Workbook Question
Re: Save Workbook Question
Doing the unload, do you mean ??
Code:
Set wb1 = ActiveWorkbook
If SheetLoaded = True Then
Set wb2 = Workbooks.Open(ThisWorkbook.Path & "\Book2.xls")
Application.DisplayAlerts = False
wb2.Sheets("Sheet2").Delete
wb1.Sheets("Sheet2").Copy After:=wb2.Sheets(wb2.Sheets.Count)
wb2.Close SaveChanges:=True
End If
On Error Resume Next
wb1.Sheets("Sheet2").Delete
On Error GoTo 0
Set wb2 = Nothing
Set wb1 = Nothing
Function SheetLoaded() As Boolean
On Error GoTo err_NoSheet
With Worksheets("Sheet2")
End With
SheetLoaded = True
Exit Function
err_NoSheet:
End Function
Re: Save Workbook Question
Quote:
I thought I could just unload it before saving, and reload it after the save but the time taken is excessive.
the code does not appear to do quite what is requested in the original post
you can try like this, as it does not open any workbooks, should be quicker
vb Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myarr As Variant, s As Worksheet
For Each s In ThisWorkbook.Sheets
If s.Name = "Sheet2" Then
Application.DisplayAlerts = False
myarr = s.UsedRange
s.Delete
Exit For
End If
Next
Cancel = True
ThisWorkbook.Save
If Not VarType(myarr) = 0 Then
Set s = ThisWorkbook.Sheets.Add
s.Name = "Sheet2"
s.Range(s.Cells(1, 1), s.Cells(UBound(myarr, 1), UBound(myarr, 2))) = myarr
End If
End Sub
test thoroughly
Re: Save Workbook Question
Thanks Westconn. Played around with this and some great stuff Koolsid gave me a few weeks ago, but I think the Workbooks are too big and the time taken overall spoils things. I can see your routine would work quite nicely - apart from losing the sheet formatting. I think now my whole idea with loading a conditional sheet as required just isn't viable
Still, interesting to try and thnks for the help :D
Regards, ABB
Re: Save Workbook Question
if you do not want to save it, what does it matter about the formatting?
you could do a save as without the sheet2, then some file renaming
Re: Save Workbook Question
>if you do not want to save it, what does it matter about the formatting?
Ideally it should remain just as before Saving i.e. continue as usable and with formatting.
Yes it was possible to save the rest of the workbook excluding this sheet, but the two outcomes (a very long time with unload/reload, or lose formatting) made neither practical. Both workbooks are 25-30 megs.
>you could do a save as without the sheet2, then some file renaming
It's for a number of other users so would need to be simple and bullet proof !
But thank you (and koolsid) for letting me explore the idea. It was worth trying and I learnt some new things :)
Regards, ABB