Results 1 to 8 of 8

Thread: [RESOLVED] Accessing data in Excel

  1. #1

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    678

    Resolved [RESOLVED] Accessing data in Excel

    I run into this problem every time I need to access an Excel worksheet.

    I can create an Excel object.
    # Set ExcelApp = CreateObject("Excel.Application") #

    I can open the workbook
    # Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
    Set ExcelSheet = ExcelWorkbook.Worksheets(1) #

    I can access the worksheet in the workbook.
    # With ExcelApp.Sheets("Sheet2") #

    BUT, I cannot get the value of any cell.
    # For Row = 3 To 5000
    If strExcelDate = .Range(Row & "A").Value Then
    strExcelRow = Row
    Exit For
    End If
    Next Row #

    On the If strExcelDate line I get error "Application-defined or object-defined error"

    when I try to write the If statement, intellisense does not five my choices. The .Range and .Value are not offered.

    Any help would be appreciated.

    Thanks

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,319

    Re: Accessing data in Excel

    Use code blocks by using the #

    You mixed all kind of references.
    Code:
    Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
    Set ExcelSheet = ExcelWorkbook.Worksheets(1)
    Then to access the values you use:
    Code:
    ExcelApp.Sheets("Sheet2")

  3. #3

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    678

    Re: Accessing data in Excel

    I just tried your suggestion, Arnoudtv. When I enter the function the word .Sheets lights up in blue. I get a msg "Invalid use of property"

  4. #4

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    678

    Re: Accessing data in Excel

    Maybe the problem is one of these declarations

    Code:
    Dim ExcelApp          As Excel.Application           Dim ExcelWorkbook As Excel.Workbook
               Dim ExcelSheet       As Excel.Worksheet

  5. #5
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,319

    Re: Accessing data in Excel

    Itís not in the declarations, itís the mixing of the 2 references
    You use the Sheets property on the ExcelApp object instead of ExcelSheet object

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    8,387

    Re: Accessing data in Excel

    Sam I am (as well as Confused at times).

  7. #7
    Member
    Join Date
    May 2021
    Posts
    32

    Re: Accessing data in Excel

    Hi

    In addition to the comments above, the Range reference is problematic:- Range(row & "A") would equate to cell 3A, rather than A3, for example.

    The following should work:

    Code:
    Set ExcelApp = CreateObject("Excel.Application")
    Set ExcelWorkbook = ExcelApp.Workbooks.Open(strDirectory & strFileName)
    Set ExcelSheet = ExcelWorkbook.Sheets("Sheet2")
    
    For TargetRow = 3 To 5000
        If strExcelDate = ExcelSheet.Range("A" & TargetRow).Value Then
            strExcelRow = TargetRow
            Exit For
        End If
    Next
    Alternatively, instead of Range, you could have written ExcelSheet.Cells(TargetRow, "A").Value.

    I hope that helps.

  8. #8

    Thread Starter
    Fanatic Member AccessShell's Avatar
    Join Date
    Oct 2013
    Posts
    678

    Re: Accessing data in Excel

    with your help I have found the problem when accessing the worksheet I need. The video was very helpful

    Also, I had the search for the target row backwards. Thanks for alerting that to me Dan_W.
    I had (Row & "A"). It should have been ("A" & Row)

    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width