Results 1 to 2 of 2

Issues with Worksheet.Intersect Method

  1. #1
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20

    Issues with Worksheet.Intersect Method

    I have two named ranges in each sheet of my export template (All named ranges have a scope of the worksheet):
    1. "Data" consists of contiguous rows where I write data and formulas
    2. "Formulas" are just the columns with formulas

    I have it this way because CopyFromRecordset only works contiguously, so contained in the recordset are text representations of the formulas, and then after the data is written, the formulas of all of the written cells in the formula range are set to the values of these cells. Here is a picture for clarity:
    Click image for larger version. 

Name:	qqq.PNG 
Views:	11 
Size:	27.2 KB 
ID:	32242
    This works perfectly the first time, but every time after that I get a 1004 error: either "Application Defined or Object Defined error" or "Method 'Intersect' of object '_Global' failed" when calling the Intersect method.
    I can't escape this situation without the task manager, and this error happens every time I try it until I restart my computer.
    Code:
    Dim XLwbk As Excel.Workbook
    Dim XLsht As Excel.Worksheet
    Dim XLrange As Range
    Dim rs As DAO.Recordset
    On Error GoTo FuncError
    ...
    Set rs = CurrentDb.OpenRecordset(..., dbOpenSnapshot)
    rs.MoveFirst
        
    Set XLsht = XLwbk.Worksheets(1)
    With XLsht
        .Activate
        Set XLrange = .range("Data")
        XLrange.CopyFromRecordset rs
        Set XLrange = .range(XLrange.Row & ":" & XLrange.Row + rs.RecordCount - 1) 'Section of "Data" range that is USED
        Dim v As Range
        For Each v In Intersect(XLrange, .range("Formulas")).Areas 'THIS LINE ERRORS
            v.FormulaR1C1 = v.Value
        Next v
    End With
    GoTo FuncSuccess
    
    FuncError:
        MsgBox Err.number & " " & Err.Description
    FuncSuccess:
        Set XLsht = Nothing: Set XLrange = Nothing: Set rs = Nothing
    Any suggestions?


    Thanks!

  2. #2
    Kluaoha is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2017
    Posts
    20
    I got it to work by specifying the Excel Application for the Intersect method.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2017, 08:14 AM
  2. Replies: 6
    Last Post: 06-25-2016, 02:56 PM
  3. Intersect
    By DB88 in forum Access
    Replies: 8
    Last Post: 06-12-2014, 01:09 PM
  4. Delete Table intersect.
    By suverman in forum Queries
    Replies: 2
    Last Post: 05-13-2011, 07:11 AM
  5. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums