How to autosave a template?

Topic

New Reply

HI I need some help with VBA

I have a Purchase Order form which has a self incrementing cell in B8
I do not want to rely on users to save the file with a different name to avoid overwriting.

Is it possible to make the file automaticlly save ie Thisworkbook.saveas for example

Something along these lines:

Saveas C:mybackuppo_ then add the po number from cell B8 so that the file would be saved as po_00001

I would like this to happen on the workbook open event if possible.

So the user would not even see the origional template.

I am using Excel 2010

Many Thanks in advance

Regards

Braddy

Reply | Quote

Replies

For example:

Code:

Private Sub Workbook_Open()   
Dim strFile As String
   With ThisWorkbook
      strFile = "c:mybackuppo_" & LCase$(.Sheets(1).Range("B8").Value) & ".xls"
      If LCase$(.FullName)  strFile Then .SaveAs strFile
   End With
      
End Sub

Reply | Quote

Hi Rory
Thanks for the reply, when I enter the code this line shows red for error Private Sub Workbook_Open()Dim strFile As String

Regards
Braddy

Reply | Quote

Sorry – that should be two separate lines. The paste seems to have messed it up. I have amended it in the original post.

Reply | Quote

Hi Rory

That’s great thanks, could I impose a littler further B8 is formatted with leading zeros, is it possible to for it to save as 00001
Thanks
Braddy

Reply | Quote

Sure – just replace .Range(“B8”).Value with .Range(“B8”).Text

Reply | Quote

Hi Rory
Sorry to bother you further but I need to add my code to increment B8, but I can’t get the sequence correct
Thanks
Braddy

Private Sub Workbook_Open()
ActiveSheet.Unprotect Password:=”aab”
With Range(“B8”)
.NumberFormat = “00000”
.Value = .Value + 1
End With
Dim strFile As String
With ThisWorkbook
strFile = “c:mybackuppo_” & LCase$(.Sheets(1).Range(“B8”).Text) & “.xls”
If LCase$(.FullName) strFile Then .SaveAs strFile(this is the part highlighted
End With
End Sub

Reply | Quote

Are you getting an error? If so, what is the message?

Reply | Quote

Hi Rory

It appears that the B8 Won’t increment and I get this message

29038-error29037-error

Regards Braddy

Reply | Quote

Hi Rory

I thought I might attach file.

Regards

Braddy29039-dpc_purchase_order-_final

Reply | Quote

Works OK for me, but you should specify the sheet, so change:

Code:

With Range("B8")

to:

Code:

With Sheets(1).Range("B8")

Reply | Quote

Hi Rory

It seems to run once then I get this 29040-error

Regards

Braddy

Reply | Quote

Hi Rory

I’m sorry if I am missing something but isn’t this in your original code

Regards
Braddy

With Sheets(1).Range(“B8”)

Reply | Quote

I was referring to the second line of your Workbook_Open code.
It would help to see the error itself, not the debug.

Reply | Quote

Hi Rory

I overlooked a problem when any body opens the saved order for instance po_00003 that increments as well which will obviously ruin the records.

Any ideas please.

Regards

Braddy

Reply | Quote

Hi Rory

I am sorry if I am confusing you, It’s not an error as such, what happens is when I open it the first time it works fine, then when I open it a second time the number in B8 does not increase by 1, the message I get is the warning that there is already there is already a file with this name do you want to overwrite. As shown above.
I cant’ understand why B8 won’t increment on the second and subsequent runs.

Hope this is clearer

Many Thanks
Braddy

Reply | Quote

Did you make the change I suggested?

Reply | Quote

Hi Rory
I did make the change you suggested the full code as below
Thanks

Private Sub Workbook_Open()

Dim strFile As String
With ThisWorkbook
strFile = “c:mybackuppo_” & LCase$(.Sheets(1).Range(“B8”).Text) & “.xls”
If LCase$(.FullName) strFile Then .SaveAs strFile

End With
With Sheets(1).Range(“B8”)
.NumberFormat = “00000”
.Value = .Value + 1
End With

End Sub

Reply | Quote

You’ve moved the save part above the part that increments the number, so you are trying to save with the initial value. Put it back where it was and you should be fine.

Reply | Quote

Hi Rory

I have done as you said, but it still comes up with the debug

29043-error

Many Thanks

Braddy

Reply | Quote

Ahh, you aren’t saving the template, just a copy, so the template number never increments. Try this one:

Code:

Private Sub Workbook_Open()
   Dim strFile           As String
   ActiveSheet.Unprotect Password:="aab"
   With Range("B8")
      .NumberFormat = "00000"
      .Value = .Value + 1
      ' need to save to store the new number
      ThisWorkbook.Save
   End With
   With ThisWorkbook
      strFile = "c:mybackuppo_" & LCase$(.Sheets(1).Range("B8").Text) & ".xls"
      If LCase$(.FullName)  strFile Then .SaveAs strFile
      ActiveSheet.Protect Password:="aab"
   End With


End Sub

Reply | Quote

Hi Rory

I can’t thank you enough, worked like a dream.

PS where do you get your patience from I would like to get some 🙂

Regards

Braddy

Reply | Quote

I have a two year old and an 8 week old at home. I’m too tired to get impatient. 🙂

Reply | Quote

Something like this:

Code:

Private Sub Workbook_Open()   Const cstrFILEPATH    As String = "c:mybackuppo_"
   Const cstrPASSWORD    As String = "aab"
   
   Dim strFile           As String


   With ThisWorkbook
      ' check if filename matches PO number
      ' if not, it's the template, so update number and save template
      ' and then save copy as new PO number
      strFile = cstrFILEPATH & LCase$(.Sheets(1).Range("B8").Text) & ".xls"
      If LCase$(.FullName)  strFile Then


         With .ActiveSheet
            .Unprotect Password:=cstrPASSWORD
            With .Range("B8")
               .NumberFormat = "00000"
               .Value = .Value + 1
            End With
            .Protect Password:=cstrPASSWORD
         End With
         ' need to save to store the new number
         .Save


         strFile = cstrFILEPATH & LCase$(.Sheets(1).Range("B8").Text) & ".xls"
         .SaveAs strFile
      End If
   End With




End Sub

should do it I think.

Reply | Quote

Hi Rory

Once again I am in your debt, that worked fine

I get this message when I re-open the saved po_ is it a problem or do I just have to live with it?
The file you are trying to open,po_000008.xls is in different format than specified by the file extension.Verify that the file is not corrupted and is from a trusted source before opening the file. do you want to open the file now?

It doesn’t increment the saved file, but may confuse the user

Hope the children didn’t keep you awake last night 🙂

Regards

Braddy

Reply | Quote

Which version of Excel are you using? If 2007 or later, then change the SaveAs line to:

Code:

[FONT font=monospace].SaveAs strFile, xlExcel8[/FONT]

Not too bad last night – only woken at 2.30 and 5am, and I get up at 5.30 anyway. 🙂

Reply | Quote

Hi Rory

have changed code as suggested no joy, naive question is it referring the the difference between xls and xlsm?

Regards

Braddy

Reply | Quote

Yes. What format is the template file? Is it xls or xlsm? And which version of Excel are you using?

Reply | Quote

Hi Rory

Forgot to say I am using Excel 2010

Regards

Braddy

Reply | Quote

Hi Rory

It’s xlsm, there are no macros as such only VBA code

Regards

Braddy

Reply | Quote

Macros are VBA code, typically.
In that case the saveas should work. Can you post a workbook version that fails?

Reply | Quote

Hi Rory

I will try to explain, when I run the template,with the benefit of your code it runs perfectly and saves the file just as it should no problem, it is only when you open the saved file you get message about the extension, if you say yes to open that’s fine as well, it does not increment the saved file with name po_0000xx, In fact everything works it’s the niggle about file extension.

I have attached one of the saved files and the template.

Many thanks

Braddy

Reply | Quote

It appears that you did not add the xlExcel8 part to the template file. If you do that, it works fine.

Reply | Quote

Hi Rory

Sorry I refer you to my signature, Sorry to be a drag, but now I am getting the comparability checker warning, please say if I am being a nuisance.

Regards

Braddy

Reply | Quote

🙂

Add:

Code:

ThisWorkbook.CheckCompatibility = False

to the code before the saveas.

Reply | Quote

Hi Rory

Everything seem to be working now, please accept my sincere thanks for all your help.

Regards

Braddy

Reply | Quote

Any time. 🙂

Reply | Quote