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 |
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 |
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 |
All times are GMT +1. The time now is 05:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com