Hi,

I have one doubt in dynamic creation of excel. I am creating excel
using visual basic. I have one dll (name as AGEXcelwritter) that is
useful for write the data in to the excel. I have another class name
cReport. i call creport class from my ASP. creport class writes some
data in to excel using AGExcelwritter dll. If I run the creport class
in run mode, it works fine. Excel created successfully. After i created
dll for cReport. But this time it is not working. if i run the creport
class in run time it is working. if i use the creport dll. i got
error.

I got the error in AGExcelwritter file. I got error in this place.

sConnection = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=Excel Files"



my AgExcelwriter file is:

Public Function writeData(ByVal FilePath As String, ByRef Data As
Variant, ByVal Sheet As String) As String
On Error GoTo ErrorEvent

Dim rsData As ADODB.Recordset
Dim rsWorkbook As ADODB.Recordset
Dim sConnection As String
Dim sColsSQL As String
Dim lCols As Long
Dim lRows As Long
Dim lx As Long
Dim ly As Long
Dim aValues As Variant
Dim cmCommand As ADODB.Command

'// Data is a array
1 aValues = Data

2 If IsEmpty(aValues) Then
3 Err.Raise vbObjectError + &H110, , "Data is empty"
End If

'// Get the col count
4 lCols = UBound(aValues, 1) + 1
'// get the row count
5 lRows = UBound(aValues, 2) + 1
'// Create the Cols SQL
6 For lx = 0 To lCols - 1
7 sColsSQL = sColsSQL & "F" & CStr(lx + 1) & IIf(lx = (lCols - 1), "",
",")
Next

8 sConnection = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=Excel Files"9 sConnection = sConnection & ";Initial Catalog=" &
FilePath

'// Create Command
10 Set cmCommand = New ADODB.Command
11 cmCommand.CommandType = adCmdTable
12 cmCommand.CommandText = "[" & Sheet & "$" & "]"
13 cmCommand.ActiveConnection = sConnection

'// Get Recordset using command
14 Set rsWorkbook = New ADODB.Recordset
15 With rsWorkbook
16 .CursorLocation = adUseClient
17 .CursorType = adOpenStatic
18 .Open cmCommand, , , adLockBatchOptimistic
'.MoveFirst

'// Update data
19 For ly = 0 To lRows - 1
20 If .EOF And (ly < lRows) Then
21 .AddNew
End If

22 For lx = 0 To lCols - 1
23 .Fields(lx).Value = aValues(lx, ly)
Next

24 .MoveNext


Next
25 .UpdateBatch
26 .Close
End With

27 Set rsWorkbook = Nothing
28 Set cmCommand.ActiveConnection = Nothing
29 Set cmCommand = Nothing

Exit Function
'// Error handler for writeData
ErrorEvent:
'// TODO: Add code to tidy up procedure here
Dim sError As String
Dim i As Long

30 If Not (rsWorkbook Is Nothing) Then
31 If rsWorkbook.State = adStateOpen Then rsWorkbook.Close
32 Set rsWorkbook = Nothing
End If

33 If Not cmCommand Is Nothing Then
34 If cmCommand.State = adStateOpen Then
35 If cmCommand.ActiveConnection.Errors.Count 0 Then
36 For i = 0 To cmCommand.ActiveConnection.Errors.Count - 1
37 sError = sError & cmCommand.ActiveConnection.Errors.Item(i).Descript
ion
Next
End If
End If
38 Set cmCommand = Nothing
End If
39 ErrorHelper.RaiseError msModuleName, "writeData", sError
End Function


--
ramkumar_cpt
------------------------------------------------------------------------
ramkumar_cpt's Profile: http://www.excelforum.com/member.php...o&userid=28673
View this thread: http://www.excelforum.com/showthread...hreadid=492380