I have a simple database that consists of two forms (add & edit) and a few basic views.
What I am wanting to do is import data into my database from an Excel spreadsheet but have been unsuccessful. I have even tried exporting from Excel as CSV to import into Notes but although it seems to go through the process if fails to add the documents.
I think I must be missing something basic!
any help would be useful
Martin
Subject: Code for Import Excel To Notes
Here here is the code to import data.The only function u need to modify here is the ImportData Function
'*************************************************
'Declaration
’ From Andre Guirard
Dim Filter As String
Dim FileName As String
Dim FileTitle As String
Dim TruncName As String
Dim VaultWIPRoot As String
Dim VaultWIPUserPath As String
Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As Long
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As Long
End Type
Declare Function GetOpenFileName Lib “comdlg32.dll” Alias “GetOpenFileNameA” (OPENFILENAME As tagOPENFILENAME) As Long
Dim OPENFILENAME As tagOPENFILENAME
Public Const OFN_ALLOWMULTISELECT = &H200
Public Const OFN_CREATEPROMPT = &H2000
Public Const OFN_ENABLEHOOK = &H20
Public Const OFN_ENABLETEMPLATE = &H40
Public Const OFN_ENABLETEMPLATEHANDLE = &H80
Public Const OFN_EXPLORER = &H80000
Public Const OFN_EXTENSIONDIFFERENT = &H400
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_LONGNAMES = &H200000
Public Const OFN_NOCHANGEDIR = &H8
Public Const OFN_NODEREFERENCELINKS = &H100000
Public Const OFN_NOLONGNAMES = &H40000
Public Const OFN_NONETWORKBUTTON = &H20000
Public Const OFN_NOREADONLYRETURN = &H8000
Public Const OFN_NOTESTFILECREATE = &H10000
Public Const OFN_NOVALIDATE = &H100
Public Const OFN_OVERWRITEPROMPT = &H2
Public Const OFN_PATHMUSTEXIST = &H800
Public Const OFN_READONLY = &H1
Public Const OFN_SHAREAWARE = &H4000
Public Const OFN_SHAREFALLTHROUGH = 2
Public Const OFN_SHARENOWARN = 1
Public Const OFN_SHAREWARN = 0
Public Const OFN_SHOWHELP = &H10
'*************************************************
'Initialize
Sub Initialize
'This agent is used to import the data from Excel to Lotus Notes
'Create the COM object for the Excel file to import and hide it
Set varXLFile = CreateObject("Excel.Application")
varXLFile.Visible = False
Set varXLWorkbook = Nothing
'Prompt for the name of the file and try to open it
Call OpenCommDlg ("Select a File")
If FileName = "" Then
strXLFilename = "R:\Enterprise Networking Team\Domino Team\1. Projects\Active\APSCI\TerritoryCode.xls"
Else
strXLFilename = FileName
End If
varXLFile.Workbooks.Open strXLFilename
Set varXLWorkbook = varXLFile.ActiveWorkbook
Set varXLSheet = varXLWorkbook.ActiveSheet
'Create new document to save data for each row
Dim s As New NotesSession
Dim db As NotesDatabase
Set db = s.CurrentDatabase
Dim doc As NotesDocument
'Loop through all valid rows and call the function to read the values into lists
'Information to be imported from Excel
Call ImportData (db, varXLSheet, doc)
'Close the Excel objects
varXLWorkbook.Close False
varXLFile.Quit
Set varXLFile = Nothing
End Sub
'****************************************************************
Function ImportData (db As NotesDatabase, varXLSheet As Variant, doc As NotesDocument)
Dim ingRow As Double 'Row
lngRow = 2
While Not (varXLSheet.Cells(lngRow, 1).Value = "")
Set doc = db.CreateDocument()
doc.Form = "Your Form Name HERE" 'Form Name
Call doc.ReplaceItemValue( "itemName1", varXLSheet.Cells(lngRow, 1).Value) 'The Field Name - With Column A Value
Call doc.ReplaceItemValue( "itemName2", varXLSheet.Cells(lngRow, 2).Value) 'The Field Name - With Column B Value
'Add more here if you need… in similar format
Call doc.ComputeWithForm(False, False)
Call doc.Save(True, False)
lngRow = lngRow +1
Wend
End Function
'****************************************************************
Function OpenCommDlg (strTitle)
Dim Title As String
Dim DefExt As String
Dim szCurDir As String
Dim APIResults%
'Give the dialog a caption title.
If strTitle = "" Then
Title = "Add supporting document" & Chr$(0)
Else
Title = strTitle
End If
'Allocate string space for returned strings
FileName = Chr$(0) & Space$(255) & Chr$(0)
FileTitle = Space$(255) & Chr$(0)
'If the user does not specify an extension, append TXT.
DefExt = "TXT" & Chr$(0)
'Set up the default directory
szCurDir = Curdir$ & Chr$(0)
'Set up the data structure before you call the GetOpenFileName
OPENFILENAME.lStructSize = Len(OPENFILENAME)
'If the OpenFile Dialog box is not linked to any form use this line.
'It will pass a null pointer.
OPENFILENAME.hwndOwner = 0&
OPENFILENAME.lpstrFilter = Filter
OPENFILENAME.nFilterIndex = 1
OPENFILENAME.lpstrFile = FileName
OPENFILENAME.nMaxFile = Len(FileName)
OPENFILENAME.lpstrFileTitle = FileTitle
OPENFILENAME.nMaxFileTitle = Len(FileTitle)
OPENFILENAME.lpstrTitle = Title
OPENFILENAME.Flags = OFN_FILEMUSTEXIST
OPENFILENAME.lpstrDefExt = DefExt
OPENFILENAME.hInstance = 0
OPENFILENAME.lpstrCustomFilter = 0
OPENFILENAME.nMaxCustFilter = 0
OPENFILENAME.lpstrInitialDir = szCurDir
OPENFILENAME.nFileOffset = 0
OPENFILENAME.nFileExtension = 0
OPENFILENAME.lCustData = 0
OPENFILENAME.lpfnHook = 0
OPENFILENAME.lpTemplateName = 0
'This will pass the desired data structure to the Windows API,
'which will in turn it uses to display the Open Dialog form.
APIResults% = GetOpenFileName(OPENFILENAME)
If APIResults% <> 0 Then
FileName = Cstr( OPENFILENAME.lpstrFile )
FileTitle = Cstr( OPENFILENAME.lpstrFileTitle )
OpenCommDlg = 1
Else
OpenCommDlg = 0
End If
End Function
Subject: Re:Code for Import Excel To Notes
Hi
I have the same requirement but my problem is that I ahve to do it thruogh web interface.
I tried this code but it works in client where it asks to select file from which data has to be imported. But in web it does not display an file selecting dialog box.
Can anybody please send me the code how to achieve that.
Thanks in Advance
Subject: Import from excel
Hi,
Try this http://www.botstation.com/code/excel_people_to_notes.php
Of course you have to customize, but I think is very usefull. I tried it with an excel file which contains 25 columns and 17 000 rows, and it was working.
Attila
Subject: import/export
Export as a Lotus 1-2-3 (.wk4) file from Excel and then import from there. Make sure your column headers match your field names in your forms and you’re set.