Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autogenerate new workbooks using template (excel 2003)

    Hi folks,
    I need help with the following project
    I want to use the attached file as template "templateA" for operations. The idea is to update all the operations at one spot and automatically generate separate operation workbooks by pushing info to another template "templateB" (both templates are not at default tmplate folder).

    The result will be 5 (based on current example, this number could vary) new workbooks based on templateB

    What I like to have for the new operation workbook (for example Operation 30) is as follows:

    -autosave this template B as filename = "Process A" in a different folder than templates
    -all header and footer info from Template A
    -First row in the Template B title would have Operation "30" and "Process A" in the center of the sheet
    -"steps" should be in column B starting from row 2 in template B
    -Corresponding "Process Description" should be in Col C in Template B
    -Each entry should be 10 rows apart to accommodate key notes for that step
    -Please note that operations may be added or deleted
    -Also, Steps may be added or deleted within any operation

    Any help would be greatly appreciated. Thanks
    Siz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    The following code needs to be fine-tuned in places, but it should give you a start.

    Sub CreateFiles()
    ' Path - trailing backslash is obligatory
    Const strPath = "C:Excel"

    Dim wbkCur As Workbook
    Dim wbkNew As Workbook
    Dim wshCur As Worksheet
    Dim wshNew As Worksheet

    Dim lngSourceRow As Long
    Dim lngMaxRow As Long
    Dim lngTargetRow As Long
    Dim strFilename As String
    Dim i As Long

    Set wbkCur = ThisWorkbook
    Set wshCur = wbkCur.Worksheets(1)
    lngMaxRow = wshCur.Range("C65536").End(xlUp).Row

    For lngSourceRow = 2 To lngMaxRow
    If Not (wshCur.Range("A" & lngSourceRow) = "") Then
    If lngSourceRow > 2 Then
    wbkNew.Close True, strPath & strFilename
    End If
    strFilename = wshCur.Range("C" & lngSourceRow) & ".xls"
    Set wbkNew = Workbooks.Add
    For i = wbkNew.Worksheets.Count To 2 Step -1
    wbkNew.Worksheets(i).Delete
    Next i
    Set wshNew = wbkNew.Worksheets(1)
    wshCur.Rows(1).Copy wshNew.Rows(1)
    lngTargetRow = 2
    End If
    wshCur.Rows(lngSourceRow).Copy wshNew.Rows(lngTargetRow)
    lngTargetRow = lngTargetRow + 10
    Next lngSourceRow
    wbkNew.Close True, strPath & strFilename

    Set wshNew = Nothing
    Set wbkNew = Nothing
    Set wshCur = Nothing
    Set wbkCur = Nothing
    End Sub

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Hans, Thank you for the startup code.
    Would you please explain what the following is doing?

    -If Not (wshCur.Range("A" & lngSourceRow) = "") Then
    -If lngSourceRow >2 Then
    -purpose of counter i

    Greatly appreciate the education.
    Siz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    > If Not (wshCur.Range("A" & lngSourceRow) = "") Then

    If the cell in column A in the "current" row is non-empty, that signifies a new process/operation. So it has to be handled separately.

    > If lngSourceRow >2 Then

    We want to write the data for a process/operation to a file. At the beginning, we haven't scanned any data yet, so we skip saving the file. After the loop, there is one more line to close and save the last new workbook.

    > purpose of counter i

    Most users have Excel set to create more than one worksheet in a new workbook. We don't need more than one here, so we run a small loop to delete all worksheets from the newly created workbook except for the firs one.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    <hr>Most users have Excel set to create more than one worksheet in a new workbook. We don't need more than one here, so we run a small loop to delete all worksheets from the newly created workbook except for the firs one.
    <hr>

    You can still create a workbook with only one sheet:

    <pre>Dim oNewBook As Workbook, iOldSheets As Integer
    iOldSheets = Application.sheetsinNewWorkbook
    Application.sheetsinNewWorkbook = 1
    Set oNewBook = Workbooks.Add
    Application.sheetsinNewWorkbook = iOldSheets
    </pre>


    It is a little more efficient.
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Yes, that's better. Thanks.

  7. #7
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Dear Hans,
    After long long hours, I was finally able to tweak your code (source file has changed since the last post). This is my first attempt to do any coding, however, I must acknowledge, that I could not have come this far without your startup code and numerous other posts on this forum.

    I would really appreciate any scrutiny done on the code for two reasons

    1) The comments or suggestions are the best way of learning
    2) I want to expand the code to perform some other tasks which are as follows (and I need guidance on how to accomplish those; on some I have exhausted my efforts and others I don't have a clue):

    - dynamic page border for the files created by the code

    - add text "key note: " before the text in col K, L, M (source file) when pasting in the destination cells in new files. Also, a border for key notes cell

    - each time the macro is run it creates a new directory (different from the source file dir) with the name["WI" & "date"] and save all new files in that directory

    - file names have "date" as part of the name. this include source file and all files created by the code

    - create history log for the source file and each new file generated from the code (sample log file is attached)
    name history log file as ["Operation ??_HistoryLog_"Date "]

    if and when the source file is modified, the history log of the source file and the appropriate Operation file/s is updated and change the rev level only for modified files
    so, every time we create new operation files we log the changes in same history log file for that operation

    where can the rev level be put on the Operation files (created from code) such that it get updated too - footer may be!!

    Many Many thanks for the help
    Siz

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    I have attached a version of your module with
    - Option Explicit added to enforce variable declaration.
    - Names of workbook variables made consistent.
    - Consistent indentation.
    - Selection of ranges avoided.
    - Consistent use of With ... End With
    - New folder created with name based on current date.
    - Current date in file names.
    - "Key note: " added to text copied from columns K, L and M.
    I don't know of an easy way to create dynamic page borders in Excel.
    I didn't have enough time to look into the history log; if nobody else picks it up, I might do it later.

  9. #9
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Regarding history log, Can I create the log files the same way we created operation files in the existing code (check the attachment in the previous post, pl)?

    If Not (wshCur.Range("A" & lngSourceRow) = "") Then
    If lngSourceRow > 7 Then
    wkbNew.Close True, strFilename
    End If
    'define file name
    strFilename = strPath & "" & wshCur.Range("G" & lngSourceRow).Value & "_WI" & Format(Date, "yyyymmdd") & ".xls"

    Set wkbNew = Workbooks.Add
    Set wshNew = wkbNew.Worksheets(1)

    this set of files differs from the operation files in that, they will not be created every time the code is run, instead same log files (for each operation) will be called and any modification in contents of col F & G (operation files) will be written in desired col in history log.
    So, if somebody could please guide me on how to get a file created on 1st run of code and for each re-run of code call this same file for data entry, I am determined to give it a try.
    Thanks
    Siz

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    You could use something like this pseudo code. You will have to work out the details yourself.

    Dim strLogFile As String
    Dim wbkLog As Workbook
    strLogFile = ...
    On Error Resume Next
    Set wbkLog = Workbooks.Open(strLogFile)
    If wbkLog Is Nothing Then
    ' Log file doesn't exist yet
    Set wbkLog = Workbooks.Add
    ' code to set up new log workbook goes here
    ...
    wbkLog.SaveAs strLogFile
    Else
    ' Log file already existed
    ' code to add line(s) goes here
    ...
    wbkLog.Save
    End If
    wbkLog.Close

  11. #11
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Dear Hans,
    I tried to modify the code (that you did for me) due to some changes in the procedure but had no luck with it. Please find attached a sample file with source code. Here is what I was trying to accomplish:

    1- Every time there is a value in coulumn A, template sheet (WI) is copied in a new wkbook, so each operation would end up having a separate file
    2- Columns B:E will be merged and wrap after each paste from sourcefile key notes are pasted in the next row under the associated operation step in sequential order

    When I run the code a messg pops up "cannot change part of a merged cell". I Clicked ok and it terminated the execution of the code. It did create 1 new file (Book 1) for operation A with first step "step 1 of operation A" and 3 key notes.

    I would appreciate the help . Thanks
    Siz

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Why do you copy the key notes below the steps in column B of the log sheet? That way, you keep on overwriting them.
    Why do you keep on copying B27 and A27 to C4 and C5 repeatedly within the loop? You copy the same values each time.
    Why do you merge cells in the log sheet? It doesn't seem to serve any purpose.
    Why do you keep on renaming the new worksheet repeatedly within the loop? You give it the same name each time.
    Why have you switched the order of the lines closing the log worksheet and those creating it? As a result, you end up with a worksheet that has already been closed.

  13. #13
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Dear Hans,
    I must have messed up with the original code to achieve the changes requested. In the attached file, I have hand filled the template sheet for 1st operation (Operation A) to give some idea of what I am trying to get here. For the given source file, there should be 2 more similar files be created using the same template. So, in the end I would have 3 new files (1 for each operation).

    - C4 & C5 are part of the title of the new file, I wanted to show the operation Name and Number in those cells for each operation (assuming the code is right and creates 3 files)
    - Trying to merge the cells and wrap text because pictures will be inserted in Column G later on

    The problem is, I am trying to generate 3 new files and couldn't able to do that (I am sure there are lot of flaws in the code). Once we know we are looking for 3 new files, questions 2 & 4 could be answered.

    Siz

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    Why does the log sheet contain key notes for steps 1, 2 and 3, while the Sourcefile sheet contains key notes for steps 1, 2, 11, 13 and 16?

  15. #15
    Lounger
    Join Date
    Dec 2004
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autogenerate new workbooks using template (excel 2003)

    In Sourcefile sheet, number of key notes could be different for each step. For example, Operation A, step1 has 3 key notes associated with it and same is true for Step 2 and step 3. Some of the other steps does not have any key notes to worry about. In some other case, there could be only one or two key note for a step (not shown in the sample file). So the number of key notes for any particular step could be anywhere from 0 to 3.

    In the previous post attachment, what I showed is just the 3 key notes (keynotes1, key notes2, keynote3) for step 1 (not keynotes for step 1, step 2 and step 3). I should have shown all other key notes for respective steps to avoid any confusion.

    Siz

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •