tricky macro's required

[westic]

I am very new to excel and to these forums and have a couple of possibly difficult questions.
I hope someone can help.

I have Three problems:


I. I want to create a button in a userform or floating toolbar or both which will acheive the following:

a.Create a new directory inside an existing folder on a server which will automatically be named according to input from a cell in the workbook.
b.Copy several existing folders to the new directory.(no renaming required)
c.Automatically save the workbook to one of the copied folders and rename it from a cell within the workbook.Close the current open workbook and open the newly saved workbook.


Here are some specifics.

I have an Excel workbook open called "CSE.xls"

After I have entered all the required data into several userforms i will have the following data in specific cells:

A Job Number which is a four digit number e.g. " 3897"
A Client reference e.g. "Smiths Construction"
A Project reference e.g. "Public Hospital 1"


Using the information in the cells I need to firstly create a directory (folder on our server) in and exiting folder named in this instance:
"3897_Smi_Pub"
To this new directory I need to copy four existing folders from:
"Y:Commercial Projects\Templates"
to:"Y:Commercial Projects\Jobs\5001_6000\3897_Smi_Pub"

the folders would be:

Correspondence
Shop drawings
Construction PDF
SWMS
These folders contain various word and excel documents

After these files are created I need to "save as" the workbook automatically named by the above mentioned data to something like "CSE_3897_job.xls" in the newly created directory "3897_Smi_Pub". Now a tricky bit.
I want to close and "not" save data in the open workbook"CSE.xls" then open the newly created and renamed workbook "CSE_3897_job.xls"
I would like to do all of the following in one go with one button or toolbar.
What are my chances???

2..After my newly saved and named workbook "CSE_3897_job.xls" opens I need to create numerous new folders in one of the newly copied folders, named from a range in the above worksheet.The number of folders will vary from job to job and the will always be created in the current directory in one of the newly created folders.
e.g "Y:Commercial Projects\Jobs\5001_6000\3897_Smi_Pub\Shop Drawings"

The data generated list of required Folders numbers is in a single column e.g. "3897_1" ( these auto number as required and can range from 1 to 50 folders)
e.g. "3897_2 , 3897_3" etc. etc.

Please note all the above folder creation is working already and I can create them using "ASAP Utilities" but i would like it to be as simple as possible for our team hence the simple "button" and no need to find directories.


3.How can I unput "Julian Date" into the "scale" dialogue on a chart?
I use an existing chart which I use to output a graphical "Project Schedule".This varies with project to project.
I would like to change the two inputs "minimum" and "maximum" found in the "Format Axis dialogue " from a cell.I have no problem converting date format but cannot
find any way to get the info into the requester
other than by hand.I may be missing something simple.
Any tips here?
Any help on any of these subject would be greatly appreciated.

 

[Joe Was]

Show the code you have, so far!

 

[westic]

This is all I have so far.

[/quote]Sub SaveasJob()
Dim MyFile As String
Dim sDir As String

MyFile = Sheets("JobData").Range("A1").Text

sDir = _
"D:\Commercial Projects\" & MyFile
'change the line above to where you want it saved
MkDir sDir
ActiveWorkbook.SaveAs Filename:=sDir & "\" & MyFile
Application.Quit 'this quits excel after all macros have run
End Sub

This basically creates the folder I need to start and copies,renames and saves the current workbook to it.

I need a mechanism to check first that the new folder does not already exist just to prevent trouble.


Thanks for your reply.

 

[Joe Was]

Private Function FileExists(fName) As Boolean
'Custom Sheet Function!
'Syntax: =FileExists("Drive:/Folder(s)/FileName")
'If file is found: Returns TRUE, else FALSE.
Dim myFolder As String

myFolder = Dir(fName)

If UCase(fName) = "HELP" Then
MsgBox " Syntax: =FileExists(""Drive:/Folder(s)/FileName"")" & vbCr & vbCr & _
"Like ==> =FileExists(""C:\Path\FileName.xls"")"
End If

If myFolder <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function

Sub CKForFile()
'Check for a file!
Dim folderFile As String
Dim Message, Title, Default

Message = "Enter a Drive:/Folder/FileName" ' Set prompt.
Title = "Check for File" ' Set title.
Default = "C:\Test.txt" ' Set default.
' Display message, title, and default value.
folderFile = InputBox(Message, Title, Default)
'folderFile = "C:\Path\FileName.xls"

If FileExists(folderFile) Then
MsgBox folderFile & " Does Exist!"
Else
MsgBox folderFile & " Not Found?"
End If
End Sub