Is it possible to create a new workbook from within vba with some extra bits..

[SiEn]

Hello all, wonder if anyone can help. before i delve to deep into this i will try and explain what i am after..
I have a userform that returns results based on the form back onto a sheet at this point it also generates the next ID number.
From this i then have a workbook used as a base model that will then be copied into this reference ID and used. (This is my job book for each job I do)
just thinking of trying to save time here...

Is it possible when i enter those details and it generates that ID if its possible to copy and rename a workbook (so the base workbook) into the generated ID format.

If that is, is also then possible to create an automatic hyperlink to that workbook from the ID field within excel.?

I fully appreciate i may be asking a bit much here..

Any help gratefully received.

Simon

 

[OaklandJim]

Without seeing your workbook(s) your request is somewhat abstract and not clear, to me at least. Might you provide the workbook? Fake-but-realistic data is fine. Use the link icon above.

That said, code that saves copies and renames a workbook is not very challenging.

And yes, you can have a hyperlink that opens another workbook. In a cell type in the FULL path and filename. Then go to the Insert => Link => Insert Link... Then create hyperlink menu comes up and the path/file name is in the Text to Display field. Cut that path/file name and paste it into the Address: field. Then in the Text to Display field type in the name of the file or whatever text is wanted.

You'll have to use Control X to cut and Control V to paste.

 

[SiEn]

Thanks for the reply unfortunately due to the nature of the book i couldnt put it in the public domain, i think it would take me too long to even disguise!
Is there anyway i could send it to you along with what i am after?

Simon

 

[OaklandJim]

I'd like to assist. Unfortunately I believe that doing that violates Mr Excel rules.

 

[SiEn]

fully understand I do not want to break any rules. Ill see if there is another way i can change the book up quickly!
appreciate the reply
Simon

 

[OaklandJim]

How proficient are you with Excel VBA?

How does the next ID number get generated and where does it go? A cell? The next cell in a table column?

Maybe try to describe this again.

From this i then have a workbook used as a base model that will then be copied into this reference ID and used. (This is my job book for each job I do)

Do you mean a "template" workbook that is copied?

Try again to describe this part of the request.

copy and rename a workbook (so the base workbook) into the generated ID format.

This seems to mean make a copy of the template workbook and rename it based on the ID?

Where does the hyperlink go?

 

[OaklandJim]

Regarding the worksheet that the new IDs go into...consider posting the table in the worksheet where new IDs are added. Use Mr. Excel's excellent XL2BB addin to do that. It enables you to post parts of your worksheet. See HERE for details.

 

[OaklandJim]

I have some code but I need some more information. See questions above.

 

[SiEn]

Hi, i have finally managed to remove all the company stuff and data.
Hopefully this will explain alittle more of what i mean.

If you look in the Type1 tab (password for book is se) and click on add it will generate a new id for that type. i have another workbook that my quotes are built from (Multiple tabs) this book is then copied and renamed as per the new ref id. I will then go into the order book and hyperlink to the new book. Just wondering if this can be automated.

The new book (my quote book master) is always in the same location, and the new book with the new reference is always saved to the same folder.

Simon

Order Book Blank.xlsm

Shared with Dropbox

www.dropbox.com

 

[OaklandJim]

I got the workbook file. I'll need to get some clarification from you so I understand what is needed and what to do.

Regarding

From this i then have a workbook used as a base model

Is this a separate "template" workbook file or is this a worksheet in the workbook file that you sent? If the latter do I need that template file too? If not, then is there is a worksheet within the workbook file that you sent that is used as "the base model."

Regarding

that will then be copied into this reference ID and used. . (This is my job book for each job I do)

I just do not understand this? It seems that there is a "job book" for each Ref ID that is created? Each job workbook file is created as a Ref ID is placed into the Ref ID column?

This is confusing too.

copy and rename a workbook (so the base workbook) into the generated ID format

Again, this seems to mean that there is a template workbook file that is copied to create a new job book whose name is based on the Ref ID.

You also said

i have another workbook that my quotes are built from (Multiple tabs) this book is then copied and renamed as per the new ref id.

Regarding

is also then possible to create an automatic hyperlink to that workbook from the ID field within excel.?

That is doable if I understand the process.

I apologize for not understanding! Maybe start again to describe the process step-by-step so I can understand.

Here is what I believe that I understand.

1. New Ref ID is generated by user form.
2. a new job book is created for the new Ref ID. That job book is a copy of a template workbook file.
3. Presumably some data is transferred into the job new book?
4. A hyperlink to the newly created file is placed into the cell where the new Ref ID is located.

All that said, I'm pretty sure that we can get you what is needed if you clear up my misunderstandings described above.