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.
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.