cathie
10-21-2017, 10:36 AM
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 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!