Question

Importing to Record from Excel

Asked by: jforget1

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

This question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Sign Up Now!
Asked On
2006-03-28 at 11:22:39ID: 21792415
Tags

excel

,

notes

,

import

Topic

Lotus Notes

Participating Experts
3
Points
500
Comments
15

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

Companies like CVS, Honeywell, Chevron, Toyoto, TriStar Pictures, the U.S. Army, and Accenture

Your Complete Technology Resource

900+ topic areas (and counting)

Related Solutions

  1. EditDocument  on NotesDocument
    Hi! I'm new to Notes, and have recently come upon a very stubborn problem: I want to find a specific NotesDocument in the database (I do this via LotusScript in an Action-button - no problem there) and then let the user edit this docu...
  2. Dealing with an embedded excel spreadsheet
    Hi, I am trying to work with an embedded excel spreadsheet and I am having a lot of problems. First, when a user inputs data into the spreadsheet then saves and closes, those changes are not visible to anyone else who opens the docume...
  3. Formatting an Excel spreadsheet from Notes
    Hi again (I'm busy this morning!) I've got some code that RanjeetRain kindly provided that allows me to export data from my database to Excel by running an agent - see http://www.experts-exchange.com/Applications/Email/Lotus_Notes/Q_20...
  4. Is it possible to import and export to a Excel Spre…
    I have a database that people are wanting to import infomation to and from a spreadsheet. Is this possible. This is just a question - if it is I will sumbit A new question with a bunch of points and more details. I just want to know ...
  5. Problems returning a NotesDocument object from …
    I have the following code: Sub Postopen(Source As Notesuidocument) source.Refresh originalValue = source.FieldGetText ("ReferenceNumberTxt") End Sub Sub Postsave(Source As Notesuidocument) newValue = source.FieldGetText (...
Tutorials are great ways to tackle hard problems!

Featured Articles

Read through articles written by top experts!

Get full access to the help you need.

Subscribe Now

30-day free trial. Register in 60 seconds.

The Latest Technology News and Tips

Your answer is only minutes away!

New Solutions Everyday

Because of our active community of experts, on average new questions receive their first comment in under 30 minutes.

Top Lotus Notes Questions

       

Hear What our Users are Saying

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

20130221-EE-VQP-038

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Development.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Microsoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...an excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans