I’m writing this code to search each document for part id, then export to excel. I’m putting 2 of my codes together but I’m not able to get this one quite right. Any help is appreciated. Thanks!!!
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Dim view As NotesView
Set view = db.GetView("Agreements by Customer")
Dim collection As NotesDocumentCollection
Dim part(300) As String,cuspart(300) As String,partdesc(300) As String,company_name(300) As String,visualcustomerid(300) As String,sales_engineer(300) As String,sprice(300) As String,um(300) As String,slocation(300) As String,minv(300) As String,maxv(300) As String,leadtime(300) As String,monthusage(300) As String,istatus(300) As String,idnodisp(300) As String
x = 0
searchString$ = Inputbox("Enter Part ID to Search For")
Set collection = db.FTSearch(searchString$, 0)
If collection.Count > 0 Then
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Set Excel = CreateObject( "Excel.Application" )
Excel.Visible = False
Excel.Workbooks.Add
Set xlWorkbook = Excel.ActiveWorkbook
Set xlsheet = Excel.Workbooks(1).Worksheets(1)
Excel.Windows(1).DisplayGridlines=True
Excel.ScreenUpdating = False
Excel.Calculation = -4135 'Calculation Manual
row = 1
With xlsheet
.Cells(row, 1).Value = "Part Number"
.Cells(row, 2).Value = "Customer Part Number"
.Cells(row, 3).Value = "Description"
.Cells(row, 4).Value = "Customer Name"
.Cells(row, 5).Value = "Customer Id"
.Cells(row, 6).Value = "Sales Engineer"
.Cells(row, 7).Value = "Selling Price"
.Cells(row, 8).Value = "U/M"
.Cells(row, 9).Value = "Location"
.Cells(row, 10).Value = "Minimum Inventory"
.Cells(row, 11).Value = "Maximum Inventory"
.Cells(row, 12).Value = "Lead Times"
.Cells(row, 13).Value = "Monthly Usage"
.Cells(row, 14).Value = "Internal Status"
.Cells(row, 15).Value = "Agreement Number"
End With
row = row + 1
Set doc = view.GetFirstDocument
'Go through each stocking agreement (doc)
Do Until doc Is Nothing
Print "Uploading data to Excel......please wait"
'On each stocking agreement go through each line item
rev = "N"
If doc.RevNum(0) = "Revision" Then rev = "Y"
i = 1
Do
partID = doc.GetItemValue("part"+i)
If partID(0) = "" Then Exit Do
With xlsheet
.Cells(row, 1).Value = doc.GetItemValue("part"+i)
.Cells(row, 2).Value = doc.GetItemValue("cuspart"+i)
.Cells(row, 3).Value = doc.GetItemValue("partdesc"+i)
.Cells(row, 4).Value = doc.GetItemValue("company_name")
.Cells(row, 5).Value = doc.GetItemValue("customerid")
.Cells(row, 6).Value = doc.GetItemValue("sales_engineer")
.Cells(row, 7).Value = doc.GetItemValue("sprice"+i)
.Cells(row, 8).Value = doc.GetItemValue("um"+i)
.Cells(row, 9).Value = doc.GetItemValue("slocation"+i)
.Cells(row, 10).Value = doc.GetItemValue("minv"+i)
.Cells(row, 11).Value = doc.GetItemValue("maxv"+i)
.Cells(row, 12).Value = doc.GetItemValue("leadtime"+i)
.Cells(row, 13).Value = doc.GetItemValue("monthusage"+i)
.Cells(row, 14).Value = doc.GetItemValue("istatus"+i)
If rev = "Y" Then
.Cells(row, 15).Value = doc.idnodisp(0) & " Revision " & doc.docrevision(0)
Else
.Cells(row, 15).Value = doc.idnodisp(0)
End If
End With
row = row + 1
i = i + 1
Loop
Print "Exporting from Stocking Agreement #:" & doc.idnodisp(0)
Set doc = view.GetNextDocument(doc)
Loop
Excel.ScreenUpdating = True
Excel.Calculation = -4105
Excel.Visible = True
End Sub