Consulting

Results 1 to 2 of 2

Thread: Using Access VBA to Loop through Excel Rows

  1. #1
    VBAX Newbie
    Joined
    Oct 2017
    Posts
    5
    Location

    Red face Using Access VBA to Loop through Excel Rows

    Hi All:

    I have some code that I am using to open and loop through an excel spreadsheet from an access database. This on the face seems simple enough but it is erroring out and I can't see what's wrong... Just wondering if I am missing some esoteric rule or something. Please HELP!...lol

    Private Sub Upload_Excel_Click()


    If Me.Text60.Value = "" Then
    userans = MsgBox("There is no file selected to process", vbOKOnly, "Unable to Upload File")
    Exit Sub
    End If


    Dim ExcelApp As Object
    Set ExcelApp = CreateObject("Excel.Application")


    Dim WkBk As Object
    Set WkBk = ExcelApp.Workbooks.Open(FileName:=Me.Text60.Value)


    Dim sht As Object
    Dim LastRow As Long


    Set sht = WkBk.ActiveSheet


    Dim LoanType
    Dim Street
    Dim Unit
    Dim City
    Dim State
    Dim Zip
    Dim Borrower
    Dim Vendor
    Dim DateRcvd
    Dim Wholesaler
    Dim Processed
    Dim UploadID


    Vendor = Me.Vendor.Value
    DateRcvd = Me.Date_Rec_d.Value
    Wholesaler = Me.Combo55.Value
    Processed = 1
    UploadID = DMax("UploadID", "All2") + 1


    sht.UsedRange 'Refresh UsedRange
    LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row


    ZWSID = "X1-Z99999999999"


    Dim xmldoc As MSXML2.DOMDocument60
    Dim xmlNodeList As MSXML2.IXMLDOMNodeList
    Dim myNode As MSXML2.IXMLDOMNode
    'Dim qdef As QueryDef


    For x = x To LastRow


    LoanType = Trim(WkBk.sht.range("A" & x).Value)

    It's erroring out on this LoanType line. The file is open, it's reading the last row; but, for some reason it is giving me the "Run-time error '438': Object doesn't support this property or method" message... Any help on this would be greatly appreciated!

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I see that you have started another post on a different error, have you fixed this one?

    this error may be caused the value held in that cell might not be a string, which is what Trim requires. You should assign all the cell values to a temporary String to conduct the manipulation on.

    May I ask why you are not simply importing the data in to the Database and transferring the data to your main table?

Posting Permissions

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