Master file creator

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
I want my read-only workbook to, upon opening, bring up a userform asking for a reference number, ie 11111-22-33

once this number has been entered, the workbook will create a new folder in O:\Internal\ named the same as the reference number, so would be O:\Internal\11111-22-33\

Then finally the workbook will save itself into this new folder under the name 11111-22-33MASTER close the original workbook and leave the new workbook open and not read only.

Also this new workbook needs to keep all its macros, apart from this one.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Ok ive sussed out the code thus,

Code:
Private Sub CreateMaster()
filref = InputBox("Enter File Reference Number", "File Reference")
If filref = "" Then
MsgBox "Please enter a File Reference number"
ActiveWorkbook.Close
Exit Sub
Else
Range("K8").Value = filref
End If
Dim ref
ref = ActiveSheet.Range("K8").Value
Dim FilDir, fs
FilDir = "O:\Internal\"
Dim NewFilePath
NewFilePath = FilDir & ref & "\"
Dim NewFilePath2
NewFilePath2 = FilDir & ref
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'check value of activecell
'create destination directory if it doesnt already exist
    If Dir(NewFilePath2, vbDirectory) = "" Then
        MkDir NewFilePath2
    End If
'save activebook as new workbook
SaveFile:
ActiveWorkbook.SaveAs (NewFilePath & ref & "MASTER.xls"), _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Run "Kill"
End Sub

This is doing what I want it to do but I am having trouble removing this from the Master file created.
Currently I am using this code
Code:
Sub Kill()

Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module13").CodeModule
With VBCodeMod
    StartLine = .ProcStartLine("CreateMaster", vbext_pk_Proc)
    HowManyLines = .ProcCountLines("CreateMaster", vbext_pk_Proc)
    .DeleteLines StartLine, HowManyLines
End With

End Sub

which runs after finishing the Master file creation code.
I have the CreateMaster code auto starting with workbook_open like this

Code:
Private Sub Workbook_Open()
    Run "Hide_all"
    Run "UnHideCase"
    Run "UnHideInput"
    Welcome.Show
    If ActiveSheet.Range("K8").Value = "" Then
    Run "CreateMaster"
    Else
Run "Kill"
    End If
End Sub

With this line it is telling me that
User-defined type not defined
Code:
Dim VBCodeMod As CodeModule
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
OK ive sorted it now!

All by my lonesome (with quite a bit of help from the archives!)

forgot to save original file with Extensibility 5.3 activated

silly me :LOL:
 

bbusch

New Member
Joined
Nov 20, 2006
Messages
7
Your code is exactly what I have been looking for. Is the entire code in the above post? And should it be inserted into THIS WORKBOOK?

Thanks
 

Forum statistics

Threads
1,136,926
Messages
5,678,614
Members
419,776
Latest member
mikelowski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top