-
Apr 18th, 2018, 05:40 PM
#1
[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.
-
Apr 18th, 2018, 07:23 PM
#2
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.
-
Apr 18th, 2018, 09:30 PM
#3
Lively Member
Re: Excel, copy entire worksheet, via VB6
Originally Posted by Elroy
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
-
Apr 19th, 2018, 08:54 AM
#4
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.
-
Apr 19th, 2018, 09:26 AM
#5
Lively Member
Re: [RESOLVED] Excel, copy entire worksheet, via VB6
Originally Posted by Elroy
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
-
Apr 19th, 2018, 12:11 PM
#6
Re: [RESOLVED] Excel, copy entire worksheet, via VB6
Originally Posted by Elroy
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
-
Apr 19th, 2018, 01:26 PM
#7
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.
-
Apr 20th, 2018, 05:06 AM
#8
Hyperactive Member
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
-
Apr 20th, 2018, 09:58 AM
#9
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.
-
Apr 20th, 2018, 10:59 AM
#10
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.
-
Apr 21st, 2018, 04:32 AM
#11
Hyperactive Member
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.
"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
-
Apr 21st, 2018, 12:24 PM
#12
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|