I am trying to get some script together which will allow the data from a spreadsheet to be imported into a form. I will have a spreadsheet where the Office ID is in the first column, I will have a record open in notes which will have the Office in a field on the form. I want the system to look through each row of the spreadsheet and pull in the data if it matches the office ID on the open record. The code below is close but it forces me to separate the data for each office into its own tab. On the form I will have a series of fields in a table that the data will fall into. Below is just a basic layout where Import 1a..b..c would be the data that has matched this office ID from columns A,B,C in the spreadsheet. Hopefully what I am asking for amkes sense, appreciate any assistance.
Import1a Import 1b Import 1c
Import2a Import 2b Import 2c
Import3a Import 3b Import 3c
Import4a Import 4b Import 4c
Import5a Import 5b Import 5c
Sub Click(Source As Button)
Dim session As New NotesSession
Dim workspace As New NotesUIWorkspace
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim item As NotesItem
Dim Filename As String
Set db = session.CurrentDatabase
Set uidoc = workspace.CurrentDocument
Set doc = uidoc.Document
Dim xlapp As Variant
Dim xlsheet As Variant
Dim x As Long
Filename = "C:\dlm_refresh\dlm_refres
' Excel.Workbooks.Open Filename '// Open the Excel file
Set xlapp = GetObject( Filename , "" )
shtname= doc.office_id(0) ' each sheet in excel is named with the agency 3-character id. I pull this from the doc to locate the correct sheet.
Set xlsheet = xlapp.WorkSheets( shtname )
doc.userid1 = Clng(xlsheet.range("C2").v
doc.userid2 = Clng(xlsheet.range("C3").v
doc.userid3 = Clng(xlsheet.range("C4").v
doc.userid4 = Clng(xlsheet.range("C5").v
doc.userid5 = Clng(xlsheet.range("C6").v
doc.asset1 = Clng(xlsheet.range("B2").v
doc.asset2 = Clng(xlsheet.range("B3").v
doc.asset3 = Clng(xlsheet.range("B4").v
doc.asset4 = Clng(xlsheet.range("B5").v
doc.asset5 = Clng(xlsheet.range("B6").v
Messagebox "Import done successfully."
Set xls = Nothing
Set sheet = Nothing
30 Day free trial.
30 Day free trial.
Experts Exchange gives me a day to day reference of proven solutions that provide me guidance and troubleshooting help for my own clients.- Brian B. Forte Consulting