Results 1 to 12 of 12

Thread: [RESOLVED] Excel, copy entire worksheet, via VB6

  1. #1

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Resolved [RESOLVED] Excel, copy entire worksheet, via VB6

    This is really a VBA question, but I'm doing it through VB6 so I'll leave it here.

    In certain places, I need to copy a complete Excel worksheet into another Excel file. Here's an example of how I'm currently doing it:

    Code:
    
            wbkNorms.Activate
            wshNorms.Select
            wshNorms.Cells.Select
            xls.Selection.Copy
            wbk.Activate
            wsh.Select
            wsh.Cells(1, 1).Select
            wsh.Paste
    
    Now, that code has two problems: 1) It uses the clipboard which I'm not thrilled about, and 2) with later versions of Excel, it throws an error if I have Excel.Visible=False and Excel.ScreenUpdating=False. It's those last two that are bugging me the most.

    I do a great deal of work with Excel via VB6 automation. And, in most cases, I hide the entire application and also turn screen-updating off to speed things along.

    Yes, I know that I could build some Row and nested Column loops and get it done. However, in some cases, I'm copying quite a bit of data, and Excel automation can be slow. Therefore, loops aren't an option.

    Any Ideas?

    Thanks,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  2. #2

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: Excel, copy entire worksheet, via VB6

    Well, I got it going to my satisfaction. I'm still using the clipboard but I'm not using all those .Select statements.

    Here's the procedure I put together:

    Code:
    
    Public Sub ExcelCopyWorksheet(wshSource As Object, wshDestination As Object)
        ' The contents of the destination are cleared before the copy is done.
        ' The clipboard is also cleared when done.
        '
        wshDestination.UsedRange.ClearContents
        wshSource.UsedRange.Copy
        wshDestination.Range("A1").PasteSpecial     ' xlPasteAll default, xlPasteSpecialOperationNone default.
        wshSource.Application.CutCopyMode = False
    End Sub
    
    
    And that seems to work just fine on several different versions of Excel, so I'm good to go for a while.

    Y'all Take Care,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  3. #3
    Lively Member
    Join Date
    Mar 2018
    Posts
    64

    Re: Excel, copy entire worksheet, via VB6

    Quote Originally Posted by Elroy View Post
    Well, I got it going to my satisfaction. I'm still using the clipboard but I'm not using all those .Select statements.

    Here's the procedure I put together:

    Code:
    
    Public Sub ExcelCopyWorksheet(wshSource As Object, wshDestination As Object)
        ' The contents of the destination are cleared before the copy is done.
        ' The clipboard is also cleared when done.
        '
        wshDestination.UsedRange.ClearContents
        wshSource.UsedRange.Copy
        wshDestination.Range("A1").PasteSpecial     ' xlPasteAll default, xlPasteSpecialOperationNone default.
        wshSource.Application.CutCopyMode = False
    End Sub
    
    
    And that seems to work just fine on several different versions of Excel, so I'm good to go for a while.

    Y'all Take Care,
    Elroy
    Hi Elroy,

    In my opinion, the Excel VBA provide a good method to finish this job:

    Code:
    wshSource.Copy Before:=wbDestination.Sheets(1)
    wshSource : It's the sheet you want to copy.
    wbDestination: It's the destination workbook, could be the current workbook
    Sheets(1) : The first sheet in wbDestination

    This method is better than clipboard, because it's the native method in Excel VBA. Clipboard is the method provided by operation system.

    Best Regards,

    Sniperhgy

  4. #4

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Hi gyhu,

    Yeah, I've always known that was an option. However, that's not quite doing what I wanted. Let me see if I can explain better.

    I've got a worksheet in some "source" workbook.
    I've also got a worksheet in some "destination" workbook.
    I don't want to delete either of these.
    I don't want to create any new worksheet tabs either.
    What I want is to take the contents from the source worksheets and copy them into the destination worksheet.

    That's a bit different from what you've outlined, although an option if I'm willing to start deleting and creating worksheets.

    Thanks Though,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  5. #5
    Lively Member
    Join Date
    Mar 2018
    Posts
    64

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Quote Originally Posted by Elroy View Post
    Hi gyhu,

    Yeah, I've always known that was an option. However, that's not quite doing what I wanted. Let me see if I can explain better.

    I've got a worksheet in some "source" workbook.
    I've also got a worksheet in some "destination" workbook.
    I don't want to delete either of these.
    I don't want to create any new worksheet tabs either.
    What I want is to take the contents from the source worksheets and copy them into the destination worksheet.

    That's a bit different from what you've outlined, although an option if I'm willing to start deleting and creating worksheets.

    Thanks Though,
    Elroy
    Ok, there is still a way can do it :

    Code:
    Sub Test()
        Sheet3.Range("C1:C3").Formula = Sheet1.Range("A1:A3").Formula
        Sheet1.Range("A1:A3").Copy
        Sheet3.Range("C1:C3").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End Sub
    You can do a test on the code above, the .Formula can copy not only the value, Formulas can also be copied. Then you need to copy the format.

    Best Regards,

    Sniperhgy

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Quote Originally Posted by Elroy View Post
    This is really a VBA question, but I'm doing it through VB6 so I'll leave it here.

    Thanks,
    Elroy
    I'm not, but I'll leave the redirect a bit longer. Seems like a good question to keep things lively in Office Development.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Hi Shaggy,

    I'm fine if you move it. I think gyhu and I are doing about the same thing with our approaches. It'd be good to see if the VBA folks have any other ideas.

    I wouldn't mind seeing an approach that gets out from under the clipboard, and still isn't a loop. But I'm not hopeful.

    Thanks,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  8. #8
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Hello Elroy,
    I work everyday with things like this and if you are interested in an approach that avoids the clipboard (that is very slow) and keeping only the content of the file this is what I do usually. Please note that I work with excel library from VB6 so you need to create a reference to it firstly - assuming that you prefer an early binding method.
    Code:
    Private objExcelApp As Excel.Application
    
    Private Sub Command1_Click()
        Dim R As Long, C As Long, xlsFile As String, xlArr() As Variant, col As String
        
        xlsFile = "C:\xlsSource.xlsx"     'Your source file
        Screen.MousePointer = vbHourglass
        Set objExcelApp = New Excel.Application
        
        With objExcelApp
                .Visible = False
                .Workbooks.Open xlsFile  'Open file
                .Worksheets(1).Activate  'Activate sheet you are interested in
            R = .Columns("A:A").Find("*", SearchDirection:=2).Row 'Find latest row (you can use any other method)
            C = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=2).Column 'Find latest numeric column
          col = Replace$(.Cells(R, C).Address, "$", vbNullString) 'Find address of latest cell of the table
        xlArr = .Range("A1:" & col).Value 'Load the entire table in a variant array
                .ActiveWorkbook.Close     'Close the workbook of the source file
                .Workbooks.Add            'Add a new workbook
                .ActiveSheet.Range("A1").Resize(UBound(xlArr, 1), UBound(xlArr, 2)).Value = xlArr 'Paste the entire array in your sheet
                Screen.MousePointer = vbDefault
                .Visible = True
        End With
    End Sub
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  9. #9

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Daniel,

    Thank you. It was the following lines I was looking for:

    Code:
    
    Dim xlArr() As Variant
    
    xlArr = objExcelApp.Range("A1:" & col).Value ' Load the entire table in a variant array
    
    objExcelApp.ActiveSheet.Range("A1").Resize(UBound(xlArr, 1), UBound(xlArr, 2)).Value = xlArr ' Paste the entire array in your sheet
    
    That makes perfect sense. I'll be playing around with those ideas to make sure I fully understand them.

    Thanks,
    Elroy
    Last edited by Elroy; Apr 20th, 2018 at 10:01 AM.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  10. #10

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Ok, with Daniel's piece, here's what I finally came up with. And yeah, I always use late binding, so I'm not dependent on any particular version of Office.

    Code:
    
    Public Sub ExcelCopyWorksheet(wshSource As Object, wshDestination As Object)
        ' The contents of the destination are cleared before the copy is done.
        '
        Dim vArray() As Variant
        Dim rng1 As Object
        Dim rng2 As Object
        '
        ' New method of using variant array as buffer.
        '
        wshDestination.UsedRange.ClearContents
        '
        Set rng1 = wshSource.UsedRange
        ' We've got to be careful because a worksheet with a single used cell won't make an array.
        ' In all other cases Excel makes a two-dimensional array.
        If rng1.Rows.Count = 1 And rng1.Columns.Count = 1 Then
            wshDestination.Range("A1").Value = rng1.Value
        Else
            ' The following array is always ONE based on its dimensions, and always has two dimensions (even when there's only one row or one column).
            vArray = rng1.Value
            Set rng2 = wshDestination.Range("A1").Resize(UBound(vArray, 1), UBound(vArray, 2))
            rng2.Value = vArray
        End If
        '
        ' Old Clipboard method.
        '
        'wshDestination.UsedRange.ClearContents
        'wshSource.UsedRange.Copy
        'wshDestination.Range("A1").PasteSpecial     ' xlPasteAll default, xlPasteSpecialOperationNone default.
        'wshSource.Application.CutCopyMode = False   ' Clear the clipboard.
    End Sub
    
    
    Again, Thanks,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  11. #11
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Just be careful when choosing UsedRange method to calculate the last row or column because it is misleading. Try to reproduce my example below and you will find that ActiveSheet.UsedRange.Rows.Count is not 1 but 2. Even worse, try to save this example inserting a single quotation mark in a cell - let's say A7 - and save the doc. The latest row will be from now on 7 even you clear the entire content of the sheet... This is because apostrophe ' is a special character and when it appears as the first character in a cell Excel treats the entire content as text. I have had many unpleasant surprises in the past with this method. So treat it with caution.
    Name:  Capture.PNG
Views: 771
Size:  1.1 KB
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  12. #12

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,817

    Re: [RESOLVED] Excel, copy entire worksheet, via VB6

    Thanks for the tip, Daniel. So long as it returns a range that's correct or too large, I'll stick with it. It's only some case where it returns a range that's too small that would bother me. I've never tested, but I always suspected it could get confused like that.

    Take Care,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

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



Click Here to Expand Forum to Full Width