Tip

Excel Data Import Using Script

After spending a great deal of time to import information from Excel over the
years, I wrote the following code to pull data from Excel files. The code
allows me to get the users data into a blank database where I can manipulate
it. The script assumes that the values in the first row are the names to be
used for the field names and that the data begins on the second row. It
continues to search for columns until it finds a blank cell in the first row.
After finding which columns to use it adds a document for each row in the Excel
file until it finds a blank row. Hope you find the following useful.

=============================
Jason Goodloe
jgoodloe@evolvetech.com
'Re-formated Version 'Multi-platform:

Option Public
Option Explicit


'WIN32
Declare Function W32_NEMGetFile Lib "nnotesws" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'WIN16
Declare Function W16_NEMGetFile Lib "_nem" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'OS2
Declare Function OS2_NEMGetFile Lib "inotesws" Alias "NEMGetFile" ( wUnk As
Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As
String ) As Integer
'use nnotesws for Win95 and WinNT, inotesws for OS/2, and _nem for Win16




Sub Initialize
%REM
******************************************************************************
OverView: This Function deletes all of the documents in the current
database and then re-populates the database based on data
contained in the excel file. The column heading row is used
for the field names in the notes doucments.
Input: User must supply the path and file name for the excel file
Results: Creates a Notes document for each row in the excel spreadsheet,
excluding the header row
Called By: This function is run from the agent list
Assumptions: 1. Data is on one worksheet only and that this worksheet is
the first worksheet in the workbook
2 Worksheet uses the first row as a column heading
3. The first blank column header is assumed to be the
last column to read data in the file
4. The first blank row is assumed to be the last row of the file
5. Field names are the first 20 spaces of the column heading
excluding spaces
******************************************************************************
M O D I F I C A T I O N H I S T O R Y
******************************************************************************
Date BY
Description
----------------- -------------------- ------------------------------
09/22/1999 J.Goodloe Initial Development 4.6.2
******************************************************************************
%END REM
Dim S As New NotesSession
Dim db As NotesDatabase
Dim Doc As NotesDocument
Dim item As NotesItem
Dim strCellRange$
Dim varCellValue As Variant
'File Dialog
Dim strFileName As String*256
Dim strTitle$
Dim strFilter$
'Excel Application
Dim varExcelApp As Variant
Dim varExcelWB As Variant
Dim varExcelSheet As Variant
'Valid columns list
Dim strExcelCodeList List As String
Dim FirstletterCode&
Dim LastletterCode&
'Iterate Rows
Dim intExcelRow%
Dim bIsRowBlank%
Dim bRowValueFound%

Set db = S.CurrentDatabase

'File Dialog
strFileName = Chr(0)
strTitle$ = s.Commonusername & ", select your database NOW"
strFilter$ = "MS Excel Files|*.xls|All Files|*.*|" 'Use
this format for ANY file type

If IsDefined ("WIN32") Then
If W32_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"| 'We
need to do this because the return is a NULL terminated string.
End If
Elseif IsDefined ("WIN16") Then
If W16_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"|
End If
Elseif IsDefined ("OS2") Then
If OS2_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then
strFileName = strFileName & |"|
End

This was first published in November 2000

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.