Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy one workbook to a different WB template (2000/SR2)

    I asked this question yestraday but I know now I didn't ask it correctly. What we are wanting to do is copy the sheets from one existing XLS workbook to a new workbook that is based on a different template. Assume that the existing XLS (a.xls) was created based on a template containing some custom macros and VBA code. a.xls is opened and the aim is copy the contents of the worksheets of a.xls to a new xls, call it b.xls that is opened using a different template new.xlt that contains custom macros and VBA code. Does anyone have example code for that or parts of that? I hope this explains what we are trying to do better.

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

    Re: Copy one workbook to a different WB template (2000/SR2)

    Do you want to copy the sheets with their existing names, and with all of the existing formatting to the new workbook? If so, what do you want to happen if there is already a sheet with the same name in the new book? Or, do you want to copy the sheets to existing sheets in the new workbook using the formatting of those sheets (if so are the sheet names the same)? Do you want to copy formulas or values?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Yes the idea is to copy the sheets, the sheet names, formulas, values and formatting to the new workbook thereby replacing the default sheets in the new workbook. I do not know what would be the best method to follow, remove the sheets in the new workbook and copy the existing sheets to the new workbook or just make copies of the sheets of the existing to the new ones and rename them to be the same as the old. Of course if there are more sheets in the old than in the new then that would have to be handled too. I just need some idea of how to start.

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

    Re: Copy one workbook to a different WB template (2000/SR2)

    See if the code below will get you started. This code assumes that the old workbook is named OldWB.xls, and that the new workbook is named NewWB.xls, and that both workbooks are open when the macro is run.

    <pre>Public Sub WBCopy()
    Dim oWS As Worksheet, oWSWk As Worksheet, oWSDmy As Worksheet
    Application.ScreenUpdating = False
    Set oWSDmy = Workbooks("NewWB.xls").Worksheets.Add
    oWSDmy.Name = "abcdefghijlk"
    For Each oWS In Workbooks("OldWB.xls").Worksheets
    Set oWSWk = Nothing
    On Error Resume Next
    Set oWSWk = Workbooks("NewWB.xls").Worksheets(oWS.Name)
    On Error GoTo 0
    If Not oWSWk Is Nothing Then
    Application.DisplayAlerts = False
    oWSWk.Delete
    Application.DisplayAlerts = True
    End If
    oWS.Copy After:=Workbooks("NewWB.xls").Worksheets(Workbooks ("NewWB.xls").Worksheets.Count)
    Next oWS
    Application.DisplayAlerts = False
    oWSDmy.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Oh thank you!! That is super good stuff.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    The following code will create a new workbook based on a Template (New.xlt), and copy all sheets from the parent workbook to the new book and save it as b.xls. You will need to replace path with the actual path of the template(new.xlt), and when saveing to the actual path you wish to save b.xls .

    Sub CopyToTemplate()
    Dim NewWB As Workbook, ws As Worksheet
    Application.ScreenUpdating = False
    Set NewWB = Workbooks.Add(Template:="PathNew.xlt")
    For Each ws In ThisWorkbook.Sheets
    ws.Copy After:=NewWB.Sheets(1)
    Next
    NewWB.SaveAs "Pathb.xls"
    Application.ScreenUpdating = True
    End Sub

    Andrew C

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Here is the code I am currently playing around. Note this is playing around code. In a xls in the work book open event I call the module that will migrate worksheets to a new workbook. I am getting an error when the code executes the End Function in the module MigrateWorkBook. The Migrate code works except for the error. See the attached file for the error message.


    ' old.xls ThisWorkbook object

    Private Sub Workbook_Open()

    objNewWkb = migrate("C:CopyingWorkBooksNew.xls") ' Copy the contents of this xls to the xls named "New.xls"
    Set objNewWkb = ActiveWorkbook

    End Sub

    ' MigrateWorkBook.bas module

    Option Explicit


    Dim objExistingWorkbook As Workbook
    Dim objNewWorkbook As Workbook
    Dim WkShtOld As Worksheet
    Dim WkShtNew As Worksheet
    Dim oWSWk As Worksheet
    Dim oWS As Worksheet

    Dim WkShtCount As Integer
    Dim WkShtCountTotal As Integer

    Public Function migrate(ByVal newWBTemplateNamePath As String) As Workbook


    Set objExistingWorkbook = Application.ActiveWorkbook

    Set objNewWorkbook = Workbooks.Add(Template:=newWBTemplateNamePath) '"C:EWPEAppPathsExcel Template FilesGovernmentBank Reconciliation gov.XLT")



    Application.ScreenUpdating = False

    For Each oWS In objExistingWorkbook.Worksheets
    Set oWSWk = Nothing
    On Error Resume Next
    Set oWSWk = objNewWorkbook.Worksheets(oWS.Name)
    On Error GoTo 0
    If Not oWSWk Is Nothing Then
    Application.DisplayAlerts = False
    oWSWk.Delete
    Application.DisplayAlerts = True
    End If
    oWS.Copy After:=objNewWorkbook.Worksheets(objNewWorkbook.Wo rksheets.Count)
    Next oWS
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    Set migrate = objNewWorkbook

    Set objNewWorkbook = Nothing
    Set objExistingWorkbook = Nothing
    Set oWS = Nothing
    Set oWSWk = Nothing


    'Error occurs on the next line. See attachment
    End Function

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Jim,

    It's a bit confusing as to what is going on here as I have not replicated your setup.

    You seem to be passing the name of a new workbook to your migrate function, but the function is using this name as the template from which to create the new workbook.

    Does the template you wish to use to create the new workbook contain any data (other than code). If not I would suggest that just have one blank sheet (with a sheet name that will not be contained in the old workbook) in the template and when you have copied your sheets from the old workbook delete that one sheet. If you use the Copy After you can use NewWorkbook.Sheets(1).

    I'm not convinced that using a Function is the best approach. Why not just incorporate the functionality in the procedure that calls it.


    Andrew C

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

    Re: Copy one workbook to a different WB template (2000/SR2)

    In yout Workbook_Open routine, change the like:

    <pre> objNewWkb = migrate("C:CopyingWorkBooksNew.xls") ' Copy the contents of this xls to the xls named "New.xls"

    to

    Set objNewWkb = migrate("C:CopyingWorkBooksNew.xls") ' Copy the contents of this xls to the xls named "New.xls"
    </pre>

    Legare Coleman

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

    Re: Copy one workbook to a different WB template (2000/SR2)

    Andrew: if the new workbook contains sheets with the same name as sheets in the old workbook, you will end up with the sheet names having (2) after them.
    Legare Coleman

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    I think that your FUNCTION should be a SUB since it has executable code in it.

    Steve

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

    Re: Copy one workbook to a different WB template (2000/SR2)

    Functions can't have executable code? What can you put in a function then? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Legare Coleman

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Bad term perhaps.
    I was thinking code like copy, moving sheets, Formatting sheets, inserting /deleting: "physical manipulations" of the worksheets and/or workbooks.

    I thought those were meant to be in SUBs, not FUNCTIONs and gave problems in functions.

    Steve

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Not really. The advantage of using functions is that you can return a result for the operation; here's one of mine that gets a WB revision number, and does some opening and closing of files if necessary:

    Function intRevisionNum(strFilePath As String, strFileName As String) As Integer
    Application.ScreenUpdating = False
    Dim wbOpenWB As Workbook
    Dim boolAlreadyOpen As Boolean
    intRevisionNum = 0
    If FileExists(strFilePath & strFileName) Then ' file exists
    For Each wbOpenWB In Application.Workbooks 'see if it's open
    If wbOpenWB.Name = strFileName Then boolAlreadyOpen = True
    Next wbOpenWB
    If Not boolAlreadyOpen Then _
    Workbooks.Open strFilePath & strFileName, ReadOnly:=True
    On Error Resume Next
    intRevisionNum = Workbooks(strFileName).BuiltinDocumentProperties(" Revision Number").Value
    If Err.Number <> 0 Then intRevisionNum = 0
    If Not boolAlreadyOpen Then
    Application.DisplayAlerts = False
    Workbooks(strFileName).Close
    Application.DisplayAlerts = True
    End If
    End If
    Set wbOpenWB = Nothing
    Application.ScreenUpdating = True
    End Function

    Many programmers will tell you that -every- subroutine should be a function after the intial calling routine.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy one workbook to a different WB template (2000/SR2)

    Legare,

    I knew this but as sheets were to be copied into a new workbook based on a template I thought it should be easy enough to ensure that sheet names would not be duplicated in the two workbooks. I did make a more explicit suggestion to that effect in a later post.

    Andrew

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
  •