Good Morning to Everybody.
Can we write coding as follows for web application.
Dim filename As Variant
Set filename = CreateObject(“Excel.Application”)
In Detail:-
i want to set some field’s value from Excel and which should show in web.
i am easily setting field value for Notes Client.
But unable to set when i am trying for Web.
Please reply quickly.
Subject: Can we create Excel application on web? Yes.
But only from Internet Explorer. Place on your web page:
Call sub by button or any other way.
You also need to enable ActiveX on Explorer.
To my mind, it is also near to ideal solution for reporting from web applications.
Subject: Can we create Excel application on web?
Hi,
I use a technique I call “client siding”, basically you load the data into a hidden div in the browser, the user clicks on a button that runs some javascript to copy the data to the windows clipboard and redirects the user to download an excel/word template. The template file has some vba code that runs as soon as it is open that processes the clipboard data and builds the spreadsheet, or mail merge etc …
I have a couple of examples at:
http://thehojusaram.blogspot.com/2006/09/another-example-of-client-siding-using.html
Merry Christmas.
Subject: Can we create Excel application on web?
Post your code, at least…
If all you want is to display the data (IE, you don’t need to interact with the spreadsheet), you can use an agent that runs on the WQO event (or a schedule, or be triggered manually as an import) that creates the documents and then display them by building an HTML table. You can use the MS active-x control to display any html table as an embedded excel plug-in. As an alternative, you simply style your html table and use javascript to do some of the things available in the plug-in, such as sorting and row highlighting, and avoid using the control (it is available if you have MS-Office 2000 or later installed on your workstation).
At any rate, here is code to import an excel spreadsheet into a Notes application, creating a document for each row:
Sub Click(Source As Button)
Dim session As New notessession
Dim db As notesdatabase
Dim doc As notesdocument
Dim pdoc As NotesDocument
Dim clog As NotesLog
Dim fileNum As Integer, cells As Integer, k As Integer
Dim InputStr As String, delimiter As String
Dim FieldArray As Variant
Dim cHeader, rHeader As String
Dim view As NotesView
Dim hview As NotesView
Dim collection As NotesViewEntryCollection
Dim entry As NotesViewEntry
Set db = session.CurrentDatabase
Set cLog = New NotesLog _
( "Script in " + _
db.Title + " on " + db.Server )
On Error Goto ERRHANDLER
Call cLog.OpenNotesLog( "dsys/steinmart", "badchecklog.nsf" )
Call cLog.LogAction("Database variables Initialized.")
Set pdoc = db.GetProfileDocument("settings")
Set view = db.GetView("tempdocs3")
Set hview = db.GetView("(Bad Checks Header Information)") 'this is the lookup view for header info
Set collection = view.AllEntries
Call cLog.LogAction("Database objects initialized.")
fileNum% = Freefile()
Set fname = pdoc.GetFirstItem("filename")
Set cellcount = pdoc.GetFirstItem("numcols")
delimiter = "," ' Delimiter of your file
cells = Cint(Cstr(cellcount.values(0))) + 1 ' How many cells + 1
filename$ = Cstr(fname.values(0))
k = 0
Call cLog.LogAction("Spreadsheet variables set from profile document.")
'Delete all existing documents before running the creation process.
%REM
Set entry = collection.GetFirstEntry()
While Not(entry Is Nothing)
Set doc = entry.Document
doc.Remove(True)
Set entry = collection.GetNextEntry(entry)
Wend
%END REM
'Today's Date
n$ = Cstr(Today)
'Print n$
wdn% = Weekday(n$)
Select Case wdn%
Case 1 : d$ = "Sunday"
Case 2 : d$ = "Monday"
Case 3 : d$ = "Tuesday"
Case 4 :d$ = "Wednesday"
Case 5 : d$ = "Thursday"
Case 6 : d$ = "Friday"
Case Else : d$ = "Saturday"
End Select
look$ = d$ + " " + n$
'Check and see if the header doc needs to be created. Create if so.
Set checkdoc = hview.GetAllDocumentsByKey(look$, True)
If (checkdoc.Count = 0) Then
Call cLog.LogAction("No header document was found for this date. This is the first Run for the day. Creating a header document.")
Set doc = db.CreateDocument
doc.form = "tempHeader"
doc.cHeader = "Check Returns Last 30 Days" ' Column 4
doc.rHeader = look$ ' Column 9
Call doc.ComputeWithForm(True, False)
Call doc.Save(True, False)
Call cLog.LogAction("Header document was successfully created.")
End If
'Import data into the body documents
Open fileName$ For Input As fileNum%
Call cLog.LogAction("Obtained a handle to the file name.")
Do While Not Eof(fileNum%)
Line Input #1, InputStr$
FieldArray = parseall(InputStr$, delimiter, cells)
response% = CreateDocument(FieldArray, look$, n$)
k = k + 1
'Print "Document Created: " + Cstr(k)
Loop
Goto Done
ERRHandler:
Print Cstr(Err) + " " + Error$ + " " + Cstr(lerr)
Call cLog.LogAction( Cstr(Err) + " " + Error$ + " " + Cstr(Erl))
Resume Done
Done:
If k > 1 Then
Call cLog.LogAction(Cstr(k) + " data were successfully imported!!")
Print Cstr(k) + " data were successfully imported!!", mb_ok, "Import Data Completed."
End If
Call cLog.Close()
Close fileNum%
End Sub
Function CreateDocument(FieldArray As Variant, look As String, n As String) As Integer
Dim session As New notessession
Dim db As notesdatabase
Set db = session.currentdatabase
Dim doc As New notesdocument(db)
Dim aclen As Integer
Dim padit As String
If FieldArray(1) <> "" Then
doc.Form = "temp"
doc.Reason = FieldArray(1) 'Reason
doc.Status = fieldArray(2) 'Status
doc.tamount = FieldArray(3) 'Amount
doc.returndate = FieldArray(4) 'Return Date
doc.storenum = FieldArray(5) 'Store
doc.checknum = FieldArray(6) 'Check Number
'This may not be 4 characters as leading zeroes are removed. Pad it with Leading zeroes
aclen = Len(FieldArray(6))
If aclen < 4 Then
padit = ""
For x = aclen To 4
padit = padit + "0"
Next
FieldArray(8) = padit + FieldArray(8)
End If
doc.acctnum = FieldArray(8) 'Account Number (Last 4)
doc.rHeader = look$ ' Header (for categorization)
doc.dCreated = n$
Call doc.ComputeWithForm(True, False)
End If
Call doc.save(1,0)
End Function
Function parseall(Initialstr As String, delimiter As String, cells As Integer) As Variant
CRcr$ = Chr(13)
IniVar$ = initialstr
numchars = Len(IniVar$)
Redim cols(cells)
Redim coldata(cells)
cols (0) = 0
For numcol = 1 To cells
prevcol = cols(numcol -1 )
cols (numcol) = Instr (cols(numcol - 1) + 1, IniVar$, Delimiter$)
StartCol = Cols(numcol - 1) + 1
If Cols (numcol) = 0 Then
endcol = numchar + 1
Else
endcol = cols(numcol)
End If
FieldLength = EndCol - StartCol
If FieldLength <= 0 Then
FieldLength = 50
End If
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, FieldLength))
CRPos% = Instr (1, ColData(numcol), CRcr$)
If (CRPos% >= 1) And (numcol <=2) Then
TempStr$ = ColData(numcol)
ColData(numcol) = Trim$(Mid$(IniVar$, StartCol, CRPos%-1))
End If
If cols (numcol) = 0 Then Exit For
Next
parseall = coldata()
End Function