object variable or with block variable not set

[murthyspd]

Hi
I am trying to open an excel sheet from MS Access and using UNION to copy some columns. Here is the code


Dim App As Excel.Application
Dim rng As Excel.Range

Set App = New Excel.Application
App.Visible = True

App.Workbooks.Open CurrentProject.Path & "\Oracle_11i_Access_Request_Approvers.xlsx"

App.Workbooks.Open CurrentProject.Path & "\Approval_Details.xlsx"

Set rng = App.Union(App.ActiveSheet.Range("A:A"), App.ActiveSheet.Range("F:F"))

It gives me the following error in the last line
"Object variable or With block variable not set"

Regards
Murthy

 

[Richard Schollar]

Hi Murthy

Does it work if you purposely restrict the ranges eg to:

Set rng = App.Union(App.ActiveSheet.Range("A1:A100"), App.ActiveSheet.Range("F1:F100"))

 

[energman58]

I think Union only works with SQL which is probably why it throws the error as it thinks it is looking at a variable

If you want to combine Excel ranges in a VBA statement it is simply Range("A:A,F:F,Z:Z").Select or whatever

 

[murthyspd]

Hey Richard
Your suggestion worked. I put specific rows - range("A1:A10"). It worked. I changed it backed to range("A:A"). That worked too..

Not sure why that happened. Any idea.

Thanks for the assistance.

Regards
Murthy

 

[Richard Schollar]

Are you using xl2003 or xl2007?

 

[murthyspd]

xl 2010

 

[Norie]

Murthy

Why are you using Union anyway?

Where are you copying the columns to?

 

[murthyspd]

I need to copy 5 columns from one workbook to another. I could have done one at a time. Just wanted to use UNION

 

[Sektor]

Set rng = App.ActiveSheet.Range("A:F")