Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union only works for sometimes.
Hello,
When I use Union function in MS Access. It only works once for two-times running. For example: if it works this time, next time a error message pops up saying "Method Union of object '_global' failed", but if I run it again, it works. such happens alternatively. Any help is appreciated. the following is my code. Private Sub Command0_Click() Dim xl As Excel.Application Dim xlwb As Excel.Workbook Dim xlst As Excel.Worksheet Dim tempFile As String Set xl = CreateObject("Excel.Application") xl.Visible = True tempFile = "G:\RSBL Template2.xls" Set xlwb = xl.Workbooks.Open(tempFile) Set xlst = xlwb.Worksheets("SUMMARY") Dim R1 As Excel.Range Dim R2 As Excel.Range Dim R3 As Excel.Range Dim R4 As Excel.Range Set R1 = xlst.Columns(1) Set R2 = xlst.Columns(2) Set R3 = xlst.Columns(3) Set R4 = xlst.Columns(4) Dim rngFrom As Excel.Range Dim rngTo As Excel.Range Set rngFrom = Sheets("SUMMARY").Union(R1, R2, R3, R4) Set rngTo = xlst.Columns(1) rngFrom.Copy rngTo.Insert End Sub Thanks very much! Regards Shu |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union only works for sometimes.
Shu wrote:
Set rngFrom = Sheets("SUMMARY").Union(R1, R2, R3, R4) I am surprised that this ever works a) Union is not a method of a sheet but of the Application b) it is not referenced from xl I would expect Set rngFrom = xl.Union(R1, R2, R3, R4) However, I think the procedure could be simplified down to Private Sub Command0_Click() Dim xl As Excel.Application Dim xlwb As Excel.Workbook Dim xlst As Excel.Worksheet Dim tempFile As String Set xl = CreateObject("Excel.Application") xl.Visible = True tempFile = "G:\RSBL Template2.xls" Set xlwb = xl.Workbooks.Open(tempFile) Set xlst = xlwb.Worksheets("SUMMARY") With xlst.Columns(1) .Resize(,4).Copy .Insert End With End Sub Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union only works for sometimes.
..union should refer to the application, not a worksheet.
I don't use Access, but this code worked ok for me from MSWord: Option Explicit Private Sub Command0_Click() Dim xl As Excel.Application Dim xlwb As Excel.Workbook Dim xlst As Excel.Worksheet Dim tempFile As String Dim R1 As Excel.Range Dim R2 As Excel.Range Dim R3 As Excel.Range Dim R4 As Excel.Range Dim rngFrom As Excel.Range Dim rngTo As Excel.Range Set xl = New Excel.Application 'Set xl = CreateObject("Excel.Application") xl.Visible = True 'tempFile = "G:\RSBL Template2.xls" tempFile = "c:\my documents\excel\book2.xls" Set xlwb = xl.Workbooks.Open(tempFile) Set xlst = xlwb.Worksheets("SUMMARY") Set R1 = xlst.Columns(1) Set R2 = xlst.Columns(2) Set R3 = xlst.Columns(3) Set R4 = xlst.Columns(4) Set rngFrom = xl.Union(R1, R2, R3, R4) Set rngTo = xlst.Columns(1) rngFrom.Copy rngTo.Insert xl.DisplayAlerts = False xlwb.Close savechanges:=True xl.DisplayAlerts = True xl.Quit Set R1 = Nothing Set R2 = Nothing Set R3 = Nothing Set R4 = Nothing Set rngFrom = Nothing Set rngTo = Nothing Set xlst = Nothing Set xlwb = Nothing Set xl = Nothing End Sub And it looks like you have a reference set to the "MS excel xx Object Library" already. So I dropped the createobject() stuff. If you end up using late binding, change all those excel.workbook, excel.range, to just Objects. (like: Dim R1 as Object But it's probably better to develop with the reference (for intellisense and help), and then to change the code to late binding later. And one final question: Is there a reason you used .union at all? Set rngFrom = xlst.Range("a:d") should work ok. If that was just for testing, then be careful. I couldn't insert a discontiguous range into that first column. I just ended up with a new empty column A.) Shu wrote: Hello, When I use Union function in MS Access. It only works once for two-times running. For example: if it works this time, next time a error message pops up saying "Method Union of object '_global' failed", but if I run it again, it works. such happens alternatively. Any help is appreciated. the following is my code. Private Sub Command0_Click() Dim xl As Excel.Application Dim xlwb As Excel.Workbook Dim xlst As Excel.Worksheet Dim tempFile As String Set xl = CreateObject("Excel.Application") xl.Visible = True tempFile = "G:\RSBL Template2.xls" Set xlwb = xl.Workbooks.Open(tempFile) Set xlst = xlwb.Worksheets("SUMMARY") Dim R1 As Excel.Range Dim R2 As Excel.Range Dim R3 As Excel.Range Dim R4 As Excel.Range Set R1 = xlst.Columns(1) Set R2 = xlst.Columns(2) Set R3 = xlst.Columns(3) Set R4 = xlst.Columns(4) Dim rngFrom As Excel.Range Dim rngTo As Excel.Range Set rngFrom = Sheets("SUMMARY").Union(R1, R2, R3, R4) Set rngTo = xlst.Columns(1) rngFrom.Copy rngTo.Insert End Sub Thanks very much! Regards Shu -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union only works for sometimes.
Thanks Dave and Bill, You are right, union can not work as
a function of worksheet object, that code is my another try, but I copy it by mistake. My problem is I did not use any object before Union, then it only works sometimes. now I know I should use the Excel application object. Thanks you very much again! Regards Shu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union range | Excel Discussion (Misc queries) | |||
UNION of Arrays - is possible? | Excel Discussion (Misc queries) | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) | |||
Help w/ Union Queries | Excel Discussion (Misc queries) | |||
VBA union of two ranges | Excel Programming |