Welcome to the #1 Community for Technology Professionals.

Importing to Record from Excel

Asked by: jforget1Solved by:

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

Asked On
2006-03-28 at 11:22:39ID: 21792415
Tags

excel

,

notes

,

import

,

Lotus Notes

Topic

Lotus IBM

Comments
15

This solution is premium contentAccess this technology solution plus 4 million more as a premium member.

Become a Premium Member

  • Solve problems faster with premium solutions and 1-on-1 expert help
  • Advance your technical skills with access to training resources
  • Grow your career by networking with industry thought leaders

Try it Free

30 day free trial. Cancel anytime.

Experts Exchange is trusted by 44 of the top 50 Fortune 500 companies.

How It Works

Put the power of 100,000 technical experts to work for you. Whether you're stuck with a difficult problem, need to expand you skills, or want to network with others in your technical area, our global expert community will help you get your job done and become a better professional.

Michael Victors Independent IT Consultant California, USA

Jon McAdams Network Manager B.C., Canada

Chris Habina System Engineer London, England

Greg Reynolds IT Manager Texas, USA

Hear from Our Community

"Three years back we had a client requirement for developing an asset tracking solution that required data acquisition from different data sources. I was assigned the role of DBA. With no practical experience in database administration, I was clueless on how to proceed. Thanks to the guidance and support of Experts Exchange's vast talent pool, I was able to complete the data integration process in less than a week's time, which saved our company more than $2000 worth of man-hours and got me a promotion!"

Sherkar Bhurshan

RFID Consultant at Aplomb Global IT Consultants

"My Staff and I use EE on a daily bases to solve all kinds of issues. One answer can be worth the cost of a lifetime membership! For example, we ran into a bunch of problems when migrating our Exchange environment from 2003 to 2010. We kept running into issues and repeatedly the best and most detailed info was coming from Experts Exchange. One of the last issues was getting active sync and OWA published correctly and the partners we were using were struggling for hours. Finally I decided to go look for my own answers and was able to find what was looking for on Experts Exchange. It was a life saver!"

Jakub Hanson

Director of Infrastructure at Noel Group LLC

"A few years back, I worked for a British bank in compliance. When the bank sold its trading department to an American company, the compliance systems had to change rapidly to avoid managers being charged with not submitting compliance reports to the government, which would have been a criminal offence. Experts Exchange helped me get the job done on time."

Murray Brown

Programmer at Murbro Ltd

201402-VQP-102

Experts Exchange powers the
growth and success of technology
professionals worldwide.

Try it Free

30 day free trial. Cancel anytime.

Learn More about How It Works

Get Experts Exchange's

Career Builder Guide

Become successful in your tech career.

We will never share this with anyone.

Experts Exchange is the Network for
Technology Professionals

  • An essential resource for producing excellent work

    Experts Exchange is the tech professional’s trusted, on-demand resource for solving difficult problems, making informed decisions, and delivering excellent solutions.

  • A better way to learn technology

    With unparalleled access to technical experts, verified real-world solutions, and diverse educational content, Experts Exchange enables personalized development of technology skills.

  • The network for building your tech career

    Experts Exchange gives you the professional exposure and valued relationships key to building the career you want.

Experts Exchange powers the growth and
success of technology professionals worldwide.

Try it Free

30 day free trial. Cancel anytime.