error 1004 Method Range of Object_Global failed

[Racquet]

I haven't figured out the problem here, perhaps someone can explain where I went wrong.

I have a simple piece of vba code in Access that opens an Excel file, copies the results from a select query to cell E6 in the Excel worksheet, then copies the data from a range starting at cell E6 and does a copy/paste/special/transpose to realign the data into a horizontal range starting at cell F5.

The first time that I run the code, I get a Run-Time error "1004" Method Range of object_Global failed.

If I start the vba code over again (having closed Excel), the code runs just fine, the Excel file is opened, data is copied to cell E6, data is transposed to cell F5, and the Excel file is saved as "Test1.xls." Excel is then closed. Inspection of the Excel file that was created shows that the result was exactly as intended.

I have tried this over and over again. First time, I get an error, next time, it runs fine. Baffling.

Here is my code:

Sub EvaluateProblem() 'This code copies data to Excel and then does a Copy/Paste/Special/Transpose
 
Dim rst As DAO.Recordset
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim Xlsheet As Excel.Worksheet
Dim MySheetPath As String
 
Set Xl = New Excel.Application
Xl.Visible = True
 
' Tell it location of actual Excel file
MySheetPath = "C:\Access Development\July 26, 2010 Folder\test.xls"
 
Set XlBook = Xl.Workbooks.Open(MySheetPath)
 
' Make sure excel is visible on the screen
XlBook.Windows(1).Visible = True
 
' Define the sheet in the Workbook as XlSheet
Set Xlsheet = XlBook.Worksheets("Sheet2") ' Names the sheet to which data is copied (Sheet2)
 
' Copies the data from Query 100A to to Cell E6. Query100A is a Select query with two values.
 
Set rst = CurrentDb.OpenRecordset("Query100A") ' References Query100A
Xlsheet.Range("e6").CopyFromRecordset rst ' Copies the data from Query 100A to to Cell E6
rst.Close
Set rst = Nothing
 
With Xl.Application.ActiveWorkbook.ActiveSheet
 
Range("e6").Select
 
' Code to test whether the data is one cell only, or more than one cell
 
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
 
Else
Range(Selection, Selection.End(xlDown)).Select "Selects the entire range"
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
 
End If
 
End With
 
' Close the Excel Workbook
' Save (and disconnect from) the Workbook
XlBook.SaveAs "C:\Access Development\July 26, 2010 Folder\Test1.xls"
XlBook.Close
 
' Clean up and end with Excel worksheet NOT visible on the screen
 
Set XlBook = Nothing
Set Xlsheet = Nothing
 
Xl.Quit
Set Xl = Nothing
 
End Sub

If someone can spot my error, I would surely appreciate it. I am just about finished up with resolving the technical problems with my application.

 

[Norie]

Try using worksheet references for the ranges, without it VBA will assume the ranges you want to use are on what it considers the active sheet.

If you are lucky that will also be the worksheet you want the code, if you aren't it won't be and the code might not work properly.

You've got a With statement with a worksheet reference but you never actually use it.

You also have a reference to a worksheet earlier in the code that you use for the first part.

Why not try using that throughout the code?

The first thing to do might be to replace this.

With Xl.Application.ActiveWorkbook.ActiveSheet

With this.

With Xlsheet

Then in the rest of the code add a few dot qualifiers and drop the Select/Selection stuff.

Something like this perhaps.

    With Xlsheet

        Set rng = .Range("E6")
        ' Code to test whether the data is one cell only, or more than one cell
        If IsEmpty(rng.Offset(-1, 0)) Then
            rng.Copy
            .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                                      False, Transpose:=True

        Else
            Set rng = Range(rng, rng.End(xlDown))    'Selects the entire range
            rng.Copy
            .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                                      False, Transpose:=True
        End If
 
    End With

 

[james_lankford]

"he first time that I run the code, I get a Run-Time error "1004" Method Range of object_Global failed."

step through the code and tell us on what line you get the error

 

[Racquet]

Thanks for taking a look at this question.


The original code errored out at Range ("e6").Select

Okay, I followed Norie's advice. I did have to set a dimension for rng to make this work. And, the result that I have is exactly the same result that I had before.

The code errors out on the line that says "Set rng = Range(rng, rng.End(xlDown)).

What I am observing is exactly the same error and same result that I had before.

After making the changes that Norie suggested, my code looks as follows:

Sub EvaluateProblem() 'This code copies data to Excel and then does a Copy/Paste/Special/Transpose
    
    Dim rst As DAO.Recordset
    Dim Xl As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim Xlsheet As Excel.Worksheet
    Dim MySheetPath As String
    Dim rng

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

    ' Tell it location of actual Excel file
    MySheetPath = "C:\Access Development\July 26, 2010 Folder\test.xls"

    Set XlBook = Xl.Workbooks.Open(MySheetPath)

        ' Make sure excel is visible on the screen
    XlBook.Windows(1).Visible = True

        ' Define the sheet in the Workbook as XlSheet
    Set Xlsheet = XlBook.Worksheets("Sheet2") ' Names the sheet to which data is copied (Sheet2)

'        Copies the data from Query 100A to to Cell E6.  Query100A is a Select query with two values.
        
    Set rst = CurrentDb.OpenRecordset("Query100A")        ' References Query100A
    Xlsheet.Range("e6").CopyFromRecordset rst             ' Copies the data from Query 100A to to Cell E6
    rst.Close
    Set rst = Nothing
        
    With Xlsheet
                        
     Set rng = .Range("e6")
       
'   Code to test whether the data is one cell only, or more than one cell
        
    If IsEmpty(rng.Offset(-1, 0)) Then
        
            rng.Copy
 
 
        .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
 
        
        Else
            Set rng = Range(rng, rng.End(xlDown))
            rng.Copy
  '
            .Range("F5").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
            
'
        End If
        
        End With
        
        ' Close the Excel Workbook
        ' Save (and disconnect from) the Workbook
    XlBook.SaveAs "C:\Access Development\July 26, 2010 Folder\Test1.xls"
    XlBook.Close

    ' Clean up and end with Excel worksheet NOT visible on the screen
    
    Set XlBook = Nothing
    Set Xlsheet = Nothing

    Xl.Quit
    Set Xl = Nothing
          
End Sub

[End Code]

Again, I really appreciate your taking the time to look at this question.

 

[Norie]

Racquet

The part of the code that is still causing a problem was actually sort of a guess on my part.

The reason for that is because the use of Selection etc makes it hard to work out what the code should be/do.

You might want to try this.

Set rng = rng.CurrentRegion

That's another guess really, perhaps if you explained in words what the range should be?

It might also be worthwhile to set up a breakpoint (F9) or two in the code so you can monitor what's happening.

A good place to put them would be before the code that is causing the problem.

You should then be able to step through the code (F8) and monitor what's happening.

 

[Racquet]

Thank you for your efforts Norie.

I use breakpoints and the F8 key all the time. This is just going to have to be a process of elimination I think. The code runs fine if I delete the activity where I am copying data and transposing it. So, I will just have to figure out what is going wrong there. When I find the problem (which I hopefully will), I will post the solution.

 

[Racquet]

Okay, I think I have a solution.

Did a lot of research, looked through a bunch of web sites and postings. I found this comment on the Bytes.com web site:

Problem is the use of the Selection method, which is available in Excel but not reliably when Access is using Excel as an automation server. You don't need it, and it is considerably faster to refer directly to ranges rather than select them first. Add an Excel range object variable object to your code . . .

Perhaps this is what you folks were trying to tell me all along.

This code seems to work just fine now. I can run it over and over again without an error. I had to define some ranges, so perhaps my code is a bit klutzy.

Sub ReEvaluateandSolveProblem() 'This code copies data to Excel and then does a Copy/Paste/Special/Transpose
    
    Dim rst As DAO.Recordset
    Dim Xl As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim Xlsheet As Excel.Worksheet
    Dim MySheetPath As String
 
    Dim CellRange1 As Excel.Range
    Dim CellRange2 As Excel.Range
    Dim CellRange3 As Excel.Range
    
    Set Xl = New Excel.Application
    Xl.Visible = True

    ' Tell it location of actual Excel file
    MySheetPath = "C:\Access Development\July 26, 2010 Folder\test.xls"

    Set XlBook = Xl.Workbooks.Open(MySheetPath)

        ' Make sure excel is visible on the screen
    XlBook.Windows(1).Visible = True

        ' Define the sheet in the Workbook as XlSheet
    Set Xlsheet = XlBook.Worksheets("Sheet2") ' Names the sheet to which data is copied (Sheet2)

'        Copies the data from Query 100A to to Cell E6.  Query100A is a Select query with two values.
        
    Set rst = CurrentDb.OpenRecordset("Query100A")        ' References Query100A
    Xlsheet.Range("e6").CopyFromRecordset rst             ' Copies the data from Query 100A to to Cell E6
    rst.Close
    Set rst = Nothing
     
    Set CellRange1 = Xl.ActiveSheet.Range("E6")
    Set CellRange3 = Xl.ActiveSheet.Range("F5")
    
'   Code to test whether the data is one cell only, or more than one cell
  
       If IsEmpty(CellRange1.Offset(-1, 0)) Then

        CellRange1.Copy

            CellRange3.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
        Else
            
            Set CellRange2 = Xl.ActiveSheet.Range(CellRange1, CellRange1.End(xlDown))
            
            CellRange2.Copy

            CellRange3.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
            
        End If
        
        ' Close the Excel Workbook
        ' Save (and disconnect from) the Workbook
    XlBook.SaveAs "C:\Access Development\July 26, 2010 Folder\Test1.xls"
       
    XlBook.Close

    ' Clean up and end with Excel worksheet NOT visible on the screen
      
    Set XlBook = Nothing
    Set Xlsheet = Nothing

    Xl.Quit
    Set Xl = Nothing
    
    Set Xl = New Excel.Application
    Xl.Visible = True
    
End Sub

[End Code]

 

[Norie]

That's kind of exactly what I was trying to say.

As usual I probably wasn't clear and perhaps even clouded the matter mentioning CurrentRegion.

Can I ask if you tried using that at all?

As for defining ranges, that's actually a good thing.

One thing though is that you should use Xlsheet in place of Xl.ActiveSheet.

A bit like Selection it's not reliable.

In fact both Selection/ActiveSheet could cause you to end up with ghost instances of Excel.

Either yourself or another poster was enquiring about that earlier.

 

[Racquet]

Thank you for your comments Norie.

Yes, I did try Current Region. In fact, I pretty much try out all the suggestions that are made here. Believe me, I have a lot to learn, so anytime someone makes a suggestion, I copy it into a module and try it out.

Putting vba code together for an Access application that will control and manipulate Excel has proved to be really challenging for me. It may be that Access 2007 or Access 2010 works better, I will have to try those versions out someday.

 

[Norie]

I don't know if anything in the later versions of Access or Excel will make much difference for this sort of things.

The object models have hardly changed over the years, sure there are some added features and tweaked things but the core stuff is basically the same.

One thing you might want to look into though is the difference between DAO and ADO.

I've never really worked out which but I think one superseded the other.

Mind you you can do the same things with them, just in slightly different ways.