Importing to Record from Excel

    Question by:
    On

    Topics:

    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.
    Joe

    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_refresh.xls"
    '      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").value)
          doc.userid2 = Clng(xlsheet.range("C3").value)
          doc.userid3 = Clng(xlsheet.range("C4").value)
          doc.userid4 = Clng(xlsheet.range("C5").value)
          doc.userid5 = Clng(xlsheet.range("C6").value)
          doc.asset1 = Clng(xlsheet.range("B2").value)
          doc.asset2 = Clng(xlsheet.range("B3").value)
          doc.asset3 = Clng(xlsheet.range("B4").value)
          doc.asset4 = Clng(xlsheet.range("B5").value)
          doc.asset5 = Clng(xlsheet.range("B6").value)
          
          Call doc.save(True,True)
          
    Out:
          Messagebox "Import done successfully."
          
          Set xls = Nothing
          Set sheet = Nothing
    End Sub
    Good Question?
    0

    Featured Post

    Course: AWS Professional Certification

    These classes are designed to help you pass the AWS Certified Solutions Architect – Associate, AWS Certified Developer, and AWS Certified SysOps Administrator Associate exams—all essential to a career as an AWS professional.

    Top Expert Contributor

    Essential articles and videos from the Experts

    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    More valuable questions with Expert answers

    Question: The title says it all: is it possible to have an agent in some application X that can - create a meeting in a mail database (for the owner) - send invitations to the participants so that all standard Notes scheduling functionality works? I know...

    Answer: On OpenNTF: Demo code for C&S APIs (http://www.openntf.org/main.nsf/blog.xsp?permaLink=NHEF-9GAAG3) now with sample code (http://openntf.org/main.nsf/project.xsp?r=project/5F400847BFF75C8286257C7E00272166/summary)

    Question: Hi, I'd like to change the email address that my DOMINO server used to send NDR (mail router). That look weird but i can't found where this email is setup. Sample notification: Delivery Failure Report Your message: Executable File Violation ...

    Answer: OK, not sure which domains are held by our domino server or whether the "mysmtpgateway is also domino but.... Something internally or from externally trying to use you as a relay sends a message to "mysmtpgateway.myotherhomedomain.com...

    Question: Hi, When i attach a file in lotus notes i want to have some favourite folders to select from as opposed to having to search through my shared drive. How do i set that up - in my screenshot attached i have highlighted in yellow where i would...

    Answer: Some users would try the intuitive: they use the Windows Explorer, right-click a folder, and use the option Include in library and then Create new library or add the folder to one of the existing libraries...

    Question: Is it possible to see which databases that are in use in Domino? I tried with Activity Trends in Domino administrator but can't find any good view to see which databases that is in use.

    Answer: See log.nsf, under Usage

    201601-LO-Qu-076

    Extend your technology team with the Experts Exchange community.

    — trusted by —

    Who answers my questions?Our community has technology experts around the world.

    Sjef Bosman

    3,758

    Solutions

    Expert in:

    • Lotus IBM
    • Databases-Other
    • Email Clients
    • Email Protocols
    • Email Servers

    Rob Hutchinson

    11

    Articles

    624

    Solutions

    Expert in:

    • Components
    • Hardware-Other
    • Laptops/Notebooks
    • Networking-Other
    • Outlook

    amilie wilson

    5

    Articles

    26

    Solutions

    Amaira Sahni

    5

    Articles

    jvutechnc

    1

    Articles

    1

    Solutions

    Emmanuel Gleizer

    1

    Articles

    43

    Solutions

    RELATED TOPICS view all topics

    1. Exchange
      (194,532)
    2. Email Clients
      (18,357)
    3. Outlook
      (93,680)
    4. MS Excel
      (127,099)
    5. Email Servers
      (23,352)
    6. BlackBerry
      (7,365)
    7. Email Protocols
      (13,750)
    8. MS Access
      (214,535)
    9. Visual Basic Classic
      (163,319)
    10. Databases-Other
      (53,224)