UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Method Of "range" Error _global, Access 2010    
 
   
sneakerhead22
post Mar 12 2018, 01:51 PM
Post#1



Posts: 53
Joined: 18-July 17



I get a Method Range of Object _Global failed error due to the following code

SOMETIMES this code works SOMETIMES it doesnt. I understand the cause of it but not how to fix it. How do i specify which sheet this is selecting everytime so that it works consistently.

CODE
    Range(columnLetter & "5").Select
    Selection.AutoFill Destination:=Range(columnLetter & "5:" & columnLetter & "113"), Type:=xlFillDefault

    Range(columnLetter & "143").Select
    Selection.AutoFill Destination:=Range(columnLetter & "143:" & columnLetter & "251"), Type:=xlFillDefault

Go to the top of the page
 
sneakerhead22
post Mar 12 2018, 02:00 PM
Post#2



Posts: 53
Joined: 18-July 17




Heres my full code

CODE
Public Sub AutoUpdateCancels()

    Dim MySheetPath As String
    Dim Xl As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim XlSheet As Excel.Worksheet
    Dim formattedDate As String
    Dim lngRow As Long, intCol As Integer, db As DAO.Database, rst As DAO.Recordset, fld As DAO.Field
    Dim columnLetter As String
    Dim qdf As DAO.QueryDef
    columnLetter = DLookup("[Column]", "[tblColumnIdentifier17]", "[WED] like #" & [Forms]![frmCancelsReporting]![txtCancelsWED] & "#")
    formattedDate = Format(Date, "mm-dd-yyyy")
    MySheetPath = "M:\Chris\Weekly Pulse\Cancel Report\2018\COM\Cancels Report - 2018v2.xlsx"
    
'Open Excel and the workbook and save a backup
    Set Xl = CreateObject("Excel.Application")
    Set XlBook = Xl.Workbooks.Open(MySheetPath, True)
    Xl.Visible = True
    XlBook.Windows(1).Visible = True
    Set XlSheet = XlBook.Worksheets(11)
    'Xl.ActiveWorkbook.SaveAs FileName:="M:\Chris\Weekly Pulse\Cancel Report\Backups\COM Backup 03-12-2018.xlsx"

'Clear Detail'
    Xl.Range("A256:D371").Select
    Xl.Selection.ClearContents


'Starting Row Number'
    lngRow = 256
'Append New Detail'
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryCancelsReport")
    qdf.Parameters("EndDate").Value = [Forms]![frmCancelsReporting]![txtCancelsWED]
    Set rst = qdf.OpenRecordset()
    Xl.Cells(lngRow, 1).CopyFromRecordset rst

    
'Fill Formulas'

    Range(columnLetter & "5").Select
    Selection.AutoFill Destination:=Range(columnLetter & "5:" & columnLetter & "113"), Type:=xlFillDefault

    Range(columnLetter & "143").Select
    Selection.AutoFill Destination:=Range(columnLetter & "143:" & columnLetter & "251"), Type:=xlFillDefault
    


    Set rst = Nothing
    Set db = Nothing
    Set Xl = Nothing
    Set XlBook = Nothing
    Set XlSheet = Nothing
MsgBox ("Make sure to save over original worksheet not as backup")
End Sub
Go to the top of the page
 
sneakerhead22
post Mar 12 2018, 03:20 PM
Post#3



Posts: 53
Joined: 18-July 17



It literally works every other time I move onto the next week, very frustrating
Go to the top of the page
 
sneakerhead22
post Mar 12 2018, 03:34 PM
Post#4



Posts: 53
Joined: 18-July 17



So if I hit end, dont save the edits the VBA made to the worksheet, and then re-run it, i get no error. any ideas why?
Go to the top of the page
 
LPurvis
post Mar 13 2018, 05:23 AM
Post#5


UtterAccess Editor
Posts: 16,272
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

This is absolutely classic non-qualified automation behaviour.
If you look in your earlier code, you're fully qualifying the Excel objects:
Set XlBook = Xl.Workbooks.Open(MySheetPath, True)

But then you're switching to:
Range(columnLetter & "5").Select
Selection.AutoFill Destination:=Range(columnLetter & "5:" & columnLetter & "113"), Type:=xlFillDefault
instead of
Xl.Range(columnLetter & "5").Select
Xl.Selection.AutoFill Destination:=Xl.Range(columnLetter & "5:" & columnLetter & "113"), Type:=xlFillDefault

The results you're then getting are typical with a subsequent Excel object being created to satisfy the unqualified object, but not then being subsequently referenceable. :-)

Cheers

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:06 PM