R
ramkumar_cpt
Hi,
I have one doubt in dynamic creation of excel. I am creating exce
using visual basic. I have one dll (name as AGEXcelwritter) that i
useful for write the data in to the excel. I have another class nam
cReport. i call creport class from my ASP. creport class writes som
data in to excel using AGExcelwritter dll. If I run the creport clas
in run mode, it works fine. Excel created successfully. After
created dll for cReport. But this time it is not working. if i run th
creport class in run time it is working. if i use the creport dll.
got error.
I got the error in AGExcelwritter file. I got error in this place
sConnection = "Provider=MSDASQL.1;Persist Security Info=False;Dat
Source=Excel Files"
my AgExcelwriter file is:
Public Function writeData(ByVal FilePath As String, ByRef Data A
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;Dat
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).Description
Next
End If
End If
38 Set cmCommand = Nothing
End If
39 ErrorHelper.RaiseError msModuleName, "writeData", sError
End Functio
I have one doubt in dynamic creation of excel. I am creating exce
using visual basic. I have one dll (name as AGEXcelwritter) that i
useful for write the data in to the excel. I have another class nam
cReport. i call creport class from my ASP. creport class writes som
data in to excel using AGExcelwritter dll. If I run the creport clas
in run mode, it works fine. Excel created successfully. After
created dll for cReport. But this time it is not working. if i run th
creport class in run time it is working. if i use the creport dll.
got error.
I got the error in AGExcelwritter file. I got error in this place
sConnection = "Provider=MSDASQL.1;Persist Security Info=False;Dat
Source=Excel Files"
my AgExcelwriter file is:
Public Function writeData(ByVal FilePath As String, ByRef Data A
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;Dat
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).Description
Next
End If
End If
38 Set cmCommand = Nothing
End If
39 ErrorHelper.RaiseError msModuleName, "writeData", sError
End Functio