Creating file from a template file on OneDrive

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,471
I have a file on OnDrive which is a template financial reporting tool.

I use the following code to create an annual file from the template
VBA Code:
Sub NewFile()

If Range("FD_FileType") = "Template" Then
   Else
   MsgBox ("This file is not the Template file and cannot be used to set up a new file"), vbExclamation, "ERROR"
   
   Exit Sub
End If

boolCancel = False

Load frmYear

frmYear.Show

Unload frmYear

If boolCancel = True Then
   Exit Sub
   Else
End If

Range("FD_FileType") = ""
Range("FD_Year") = strYear

Range("LYTB_Clear").ClearContents
Range("CYTB_Clear").ClearContents

strPath = ActiveWorkbook.Path
strPath = Replace(strPath, "https://d.docs.live.net/037c9df14c70d425/", "C:\Users\User\OneDrive\")
strPath = Replace(strPath, "/", "\")

Set objFSO = CreateObject("scripting.filesystemobject")

If objFSO.folderexists(strPath & "\" & strYear) = True Then
   Else
   objFSO.createfolder (strPath & "\" & strYear)
End If

Set objFSO = Nothing

With ActiveSheet
   .Unprotect Password:=strPassword

   .Shapes("shpCreate").Fill.ForeColor.RGB = RGB(226, 240, 217)
   .Shapes("shpCreate").TextFrame.Characters.Text = "BUTTON DISABLED - ANNUAL FILE CREATED"
   .Shapes("shpCreate").TextFrame.Characters.Font.Size = 12
   
   .Protect Password:=strPassword
End With

ActiveWorkbook.SaveAs strPath & "\" & strYear & "\Financial Reporting " & strYear & ".xlsm"

MsgBox ("This file has been saved as the selected year file"), vbInformation, "FILE SAVED"

End Sub
This sucessfully creates the annual file but where the following ranges are cleared as part of setting up the new file, these are being saved to the Tam,plate file despite there being not code save the Template file other than as the annual file.
Range("FD_FileType")
Range("FD_Year")

Can anyone shed some light on this as the file is for a client and we're both finding it frustrating that Excel is not doing as it is told.


TIA
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,114,014
Messages
5,545,497
Members
410,687
Latest member
Sunshine2020
Top