Union only works for sometimes.

[Shu]

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

 

[Bill Manville]

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

 

[Dave Peterson]

..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]

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