Page 1 of 2 12 LastLast
Results 1 to 10 of 11

error 1004 Method Range of Object_Global failed

This is a discussion on error 1004 Method Range of Object_Global failed within the Microsoft Access forums, part of the Question Forums category; I haven't figured out the problem here, perhaps someone can explain where I went wrong. I have a simple piece ...

  1. #1
    Board Regular
    Join Date
    Feb 2006
    Posts
    75

    Default error 1004 Method Range of Object_Global failed

    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:

    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.
    Last edited by Joe4; Aug 12th, 2010 at 04:07 PM. Reason: fixed code tags

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,150

    Default Re: error 1004 Method Range of Object_Global failed

    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.
    Code:
    With Xl.Application.ActiveWorkbook.ActiveSheet
    With this.
    Code:
    With Xlsheet
    Then in the rest of the code add a few dot qualifiers and drop the Select/Selection stuff.

    Something like this perhaps.
    Code:
        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
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Jan 2009
    Posts
    869

    Default Re: error 1004 Method Range of Object_Global failed

    "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

  4. #4
    Board Regular
    Join Date
    Feb 2006
    Posts
    75

    Default Re: error 1004 Method Range of Object_Global failed

    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:

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

  5. #5
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,150

    Default Re: error 1004 Method Range of Object_Global failed

    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.
    Code:
    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.
    If posting code please use code tags.

  6. #6
    Board Regular
    Join Date
    Feb 2006
    Posts
    75

    Default Re: error 1004 Method Range of Object_Global failed

    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.

  7. #7
    Board Regular
    Join Date
    Feb 2006
    Posts
    75

    Smile Re: error 1004 Method Range of Object_Global failed

    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.

    [Code]
    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]

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,150

    Default Re: error 1004 Method Range of Object_Global failed

    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.
    Last edited by Norie; Aug 13th, 2010 at 10:27 PM.
    If posting code please use code tags.

  9. #9
    Board Regular
    Join Date
    Feb 2006
    Posts
    75

    Default Re: error 1004 Method Range of Object_Global failed

    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.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,150

    Default Re: error 1004 Method Range of Object_Global failed

    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.
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com