Hi Amarnath,
i have a query to export the data from defined table so could you help me on this…
Subject: export to acces from tabels
Sub Click(Source As Button)On Error Goto errHandle
Dim s As New Notessession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim kwview As NotesView
Dim kwdoc As NotesDocument
Dim lview As NotesView
Dim strDB As String
Dim dbq As String
Dim ConnectionString As String
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim colNames As String
Dim docVals As String
Dim lviewnav As NotesViewNavigator
Dim lentry As NotesViewEntry
Dim lentry2 As NotesViewEntry
Set db = s.CurrentDatabase
'First loop through all view documents and export them…
Set lview = db.GetView(“(ViewName)”)
colNames = “”
If lview.EntryCount > 0 Then
Forall vals In lview.Columns
colNames = colNames & |“| & vals.Title & |”,|
End Forall
colNames = Trim(Left$(colNames, Len(colNames) -1))
Set kwview = db.GetView(“(ApplicationKeywords)”)
Set kwdoc = kwview.GetDocumentByKey(“AccessDBPath”)
If Not kwdoc Is Nothing Then
strDB = kwdoc.keyValue(0)
End If
'Allow user to redefine Access DB path if necessary
strDB = Inputbox$("Enter full Access Database path: ",“Full Access Database path”, strDB)
dbq = “; Dbq=” & strDB
Forall DSN In con.ListDataSources
If (Instr(1,dsn, “Access”) > 0) Then ConnectionString = DSN & dbq
End Forall
If Not con.ConnectTo( ConnectionString ) Then
Messagebox "Could not connect to: " & ConnectionString
Exit Sub
End If
Set qry.Connection = con
Set result.Query = qry
Set lviewnav = lview.CreateViewNav()
Set lentry = lviewnav.GetFirst
While Not lentry Is Nothing
Set lentry2 = lentry
Set lentry = lviewnav.GetNext(lentry)
docVals = “”
Set doc = lentry2.Document
Forall vals In lentry2.ColumnValues
If ((Datatype(vals)>1 And Datatype(vals)<8) Or Left$(vals,1)=“$”) Then
docVals = docVals & vals & |,|
Else
docVals = docVals & |“| & vals & |”,|
End If
End Forall
docVals = Trim(Left$(docVals, Len(docVals) -1))
qry.SQL = |INSERT INTO “Leases” (| & colNames & |) VALUES (| & docVals & |);| '*******If I change this to a SELECT * statement, works fine
result.Execute '*******Line 69 dying here
doc.exportedtoAccess = Now
Call doc.Save(True, False)
Wend
Else
Messagebox “No documents to export.”
End If
'The get all the extra documents and export those to a different table
Set lview = db.GetView(“(OtherViewName)”)
If lview.EntryCount > 0 Then
Forall vals In lview.Columns
colNames = colNames & |“| & vals.Title & |”,|
End Forall
colNames = Trim(Left$(colNames, Len(colNames) -1))
Set lviewnav = lview.CreateViewNav()
Set lentry = lviewnav.GetFirst
While Not lentry Is Nothing
Set lentry2 = lentry
Set lentry = lviewnav.GetNext(lentry)
docVals = “”
Set doc = lentry2.Document
Forall vals In lentry2.ColumnValues
If ((Datatype(vals)>1 And Datatype(vals)<8) Or Left$(vals,1)=“$”) Then
docVals = docVals & vals & |,|
Else
docVals = docVals & |“| & vals & |”,|
End If
End Forall
docVals = Trim(Left$(docVals, Len(docVals) -1))
qry.SQL = |INSERT INTO “LeasePayments” (| & colNames & |) VALUES (| & docVals & |);|
result.Execute
doc.exportedtoAccess = Now
Call doc.Save(True, False)
Wend
Else
Messagebox “No other documents to export.”
End If
If con.IsConnected Then
result.Close(DB_CLOSE)
con.Disconnect
End If
Exit Sub
errHandle:
Msgbox(“An error in the application has occurred. Please call the” &_
" Help Desk or submit an e-Ticket online with the following" &_
" information and the steps taken to get to the error." & Chr(10) &_
Chr(13) & Chr(10) & Chr(13) & "(Export to Access) " & Lsi_info(2) & " - " & Lsi_info(12) &_
" Error: " & Error (Err) & " , number " & Err & " on line " & Erl())
Exit Sub
End Sub
Subject: export to acces from tabels
is this query meant for a particular person / the forum.
If its for a person, please email him.
If its for the forum, please provide complete details.
rg
Rakesh
Subject: export to acces from tabels
Import from Excel Sheet
Dim ws As New NotesUIWorkspace
Dim ss As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
'Set statement used to connect to current database
Set db=ss.currentdatabase
'Call statements used to import data from Excel
Dim xlapp As Variant
Dim xlbook As Variant
Dim xlsheet As Variant
Dim xlname As Variant
'Set statement used to create object for excel application and
' open excel sheet and connect to current worksheet in excel
Set xlapp=createobject("excel.application")
xlname=Inputbox("Enter the exact path (including the file name) of the Excel file from which the data to be imported.")
If xlname = "" Then
Msgbox "Discontinuing the further action since no input is supplied.",,"Discontinuing the action..."
Exit Sub
End If
'xlname="C:\Roaster.xls"
xlapp.workbooks.open xlname
Set xlbook=xlapp.activeworkbook
Set xlsheet=xlbook.worksheets(1)
'used to extract data from excel
Dim row,f As Integer
f=Inputbox("Enter the number of rows")
row=1
While row<=f
Dim id,empname,address,telno,emailid,process,processhead As String
id=xlsheet.cells(row,1).value
empname=xlsheet.cells(row,2).value
address=xlsheet.cells(row,3).value
telno=xlsheet.cells(row,4).value
emailid=xlsheet.cells(row,5).value
process=xlsheet.cells(row,6).value
processhead=xlsheet.cells(row,7).value
'A new backend document was created and set the value to document
Set doc=db.CreateDocument
With doc
.form="Employee Form"
.txtid=id
.txtEmpname=empname
.txtAddress=address
.intTelNumber=telno
.txtEmail=emailid
.txtProcess=process
.txtProcessHead=processhead
End With
Call doc.Save(True,False)
row=row+1
Wend
xlapp.quit
Export to excel sheet
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set view = db.GetView("Employee Details View")
Set doc = view.GetFirstDocument
Dim z As Variant
Dim y As Variant
Set z = createobject("Excel.Application")
z.visible=True
z.workbooks.add
z.Worksheets(1).Range("D1").Value="EMPLOYEE DETAILS IMPORTED FROM LOTUS NOTES"
z.Worksheets(1).Range("A1:G1").Interior.Color = 25242322
z.Worksheets(1).Range("A1:G1").Merge
z.Worksheets(1).Range("A1:G1").VerticalAlignment =2
z.Worksheets(1).Range("A1:G2").HorizontalAlignment = 3
z.Worksheets(1).Range("A1:Z2").Font.Bold = True
z.Worksheets(1).Range("A1:G1").RowHeight=25
z.Worksheets(1).Range("A1:AA1").Font.size=12
z.Worksheets(1).Range("A1:G1").Font.Color=55555555
z.Worksheets(1).Range("A2:G2").Interior.Color = 12121212
z.Worksheets(1).Range("A1:AZ65536").Font.name = "Tahoma"
z.Worksheets(1).Range("c1:c100").wraptext = True
'z.Worksheets(1).Range("b1:b100").VerticalAlignment =2
z.referencestyle=2
Set y=z.workbooks(1).worksheets(1)
Dim rows As Integer
rows=1
y.cells(2,1).Value = "Employee Id"
y.cells(2,1).columnwidth=12
y.cells(2,1).Font.size = 10
y.cells(2,2).Value = "Employee Name"
y.cells(2,2).columnwidth=15
y.cells(2,2).Font.size = 10
y.cells(2,3).Value="Location"
y.cells(2,3).columnwidth=14
y.cells(2,3).Font.size = 10
y.cells(2,4).Value = "Telephone"
y.cells(2,4).columnwidth=12
y.cells(2,4).Font.size = 10
y.cells(2,5).Value = "email"
y.cells(2,5).columnwidth=25
y.cells(2,5).Font.size = 10
y.cells(2,6).Value = "Process"
y.cells(2,6).columnwidth=10
y.cells(2,6).Font.size = 10
y.cells(2,7).Value = "Process Head"
y.cells(2,7).columnwidth=13
y.cells(2,7).Font.size = 10
Dim m,n,o As Double
Dim s As String
Dim i As Integer
i=0
Do While Not (doc Is Nothing)
s="A"+Cstr(3+i)+":F"+Cstr(3+i)
' z.Worksheets(1).Range(s).Borders.LineStyle=1
y.cells(i+3,1).Value= doc.GetItemValue("txtId")
y.cells(i+3,2).Value= doc.GetItemValue("txtEmpname")
y.cells(i+3,3).Value= doc.GetItemValue("txtAddress")
y.cells(i+3,4).Value=doc.GetItemValue("intTelnumber")
y.cells(i+3,5).Value=doc.GetItemValue("txtEmail")
y.cells(i+3,6).Value=doc.GetItemValue("txtProcess")
y.cells(i+3,7).Value=doc.GetItemValue("txtProcessHead")
o=n
n=m
m=o
i=i+1
Set doc=view.GetNextDocument (doc)
Loop
this will helps u
Subject: export to acces from tabels
please try this code…
Options Event:
Option Public
Uselsx “*lsxodbc”
Initialize Event:
'The following agent pushes both BRAND NEW docs and EDITED docs from Notes
to Access.
’ Write to the Contracts table in Access from the Pre Contract Data Sheet
in Notes.
Dim ses As New NotesSession
Dim curdb As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim Con As New ODBCConnection
Dim Qry As New ODBCQuery
Dim Result As New ODBCResultSet
Set Qry.Connection = Con
Set Result.Query = Qry
Con.ConnectTo(“TYPE DATASOURCE HERE”)
’ Access table name we’re pushing to and pulling from.
Qry.SQL = “SELECT * FROM Contracts”
Result.Execute
’ Print a mssg to the status bar to know the connection is good.
If Con.IsConnected Then
Print “Connection to Contracts Established.”
Else
Print “No Connection to Contracts Established.”
Exit Sub
End If
Set curdb = ses.CurrentDatabase
’ Push docs from this view.
Set view = curdb.GetView(“TYPE NOTES VIEW NAME HERE”)
Set doc = view.GetFirstDocument
Do Until (doc Is Nothing)
'If the value of the first (1) field (Contract_No) in the result set (ACCESS)
matches the value in the CTR_TrackingNo field in the first Notes doc in the
view (NOTES), then this doc already exists in Access so push all the following
values in Notes to Access…
If Result.LocateRow(1, doc.CTR_TrackNo(0)) Then
Call Result.SetValue(“Annuitant_Name”, doc.Name(0))
Call Result.SetValue(“Original_Value”, doc.Qte_ActualPrice(0))
Call Result.SetValue(“SCC_Contract_Maturity”,
doc.PC_SccLastDueDt(0))
Call Result.SetValue(“Final_Contract_Maturity”,
doc.Con_Maturity(0))
’ Save to Access.
Result.UpdateRow
Else
’ …Otherwise, if the value of the first (1) field (Contract_No)
in the result set DOES NOT match the value in the CTR_TrackingNo field in the
first Notes doc in the view, then this doc DOES NOT already exist in Access so
create a NEW doc in Access by pushing the following values in Notes to Access.
Result.AddRow
Call Result.SetValue(“Contract_No”, doc.CTR_TrackNo(0))
Call Result.SetValue(“Annuitant_Name”, doc.Name(0))
Call Result.SetValue(“Original_Value”, doc.Qte_ActualPrice(0))
Call Result.SetValue(“SCC_Contract_Maturity”,
doc.PC_SccLastDueDt(0))
Call Result.SetValue(“Final_Contract_Maturity”,
doc.Con_Maturity(0))
’ Save to Access.
Result.UpdateRow
End If
Set doc = view.GetNextDocument(doc)
Loop
’ Close the result set.
Result.Close( DB_CLOSE )
’ Disconnect from the datasource.
Con.Disconnect