-
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!
-
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
-
Forum Rules