generating an excel file in vb 6.0

[veer]

hello expert i want to generate excel file in vb through coding, i use the following code to generate excel sheet
Dim AppXls As Excel.Application
Dim ObjWb As Excel.Workbook
Dim ObjWs As Excel.Worksheet

Set AppXls = CreateObject("Excel.Application")
Set ObjWb = AppXls.Workbooks.Add

Set ObjWs = ObjWb.Worksheets.Add
ObjWs.Range("A1").Value = "1"

ObjWb.SaveAs ("C:\TestCreate.xls")

ObjWb.Close (SaveChanges = False)
but it producing the error " Add method of object workbooks failed"
please help me by editing the code

[freedom]

I assume here that your are getting data from a database say a Access or SQL? If that is the case, you must first create a connection string for that, afterwards, you can use the below code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim RSFile as ADODB.Recordset
Dim strQuery as string
Dim StrConnection as string

strConnection(your connection string here)
strQuery(Your query here)
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

Dim n As Int32
For n = 1 To RSFile.Fields.Count
oSheet.Cells(1, n).Value = RSFile.Fields(n - 1).Name
Next

'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(RSFile)

'Save the workbook and quit Excel.
oBook.SaveAs("C:\Path\Filename.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

.open (strconnection)
.execute(strquery)
.close

Hope this will help. Cheers!

[veer]

hello expert
you are write, i am trying to insert the data in excel file from sql server
but i am not properly understanding your code like int32
i am sending my code for your clarification please modify it because when i execute it produces the error on line
int32 and can i change it into integer or not
other error is in for loop
" class does't support automation or interface"
please provide some help
thanks

Private Sub Command1_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim con As ADODB.Connection
Dim RSFile As ADODB.Recordset
Dim strQuery As String
Dim StrConnection As String
Set con = New ADODB.Connection
Set RSFile = New ADODB.Recordset
StrConnection = "Driver={SQL Server};Server=Irish-vul; Database=shrmgmtDb;Uid=sa"
strQuery = "select * from output"

Set oExcel = CreateObject("Excel.Application")


Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oExcel.Visible = True
Dim n As int32

For n = 1 To RSFile.Fields.Count
oSheet.Cells(1, n).Value = RSFile.Fields(n - 1).Name
Next
'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset (RSFile)
'Save the workbook and quit Excel.
oBook.SaveAs ("C:\var\Book2.xls")
oSheet = Nothing
oBook = Nothing
'oExcel = Quit()
oExcel = Nothing
con.Open (StrConnection)
con.Execute (strQuery)
con.Close
End Sub

I assume here that your are getting data from a database say a Access or SQL? If that is the case, you must first create a connection string for that, afterwards, you can use the below code

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim RSFile as ADODB.Recordset
Dim strQuery as string
Dim StrConnection as string

strConnection(your connection string here)
strQuery(Your query here)
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

Dim n As Int32
For n = 1 To RSFile.Fields.Count
oSheet.Cells(1, n).Value = RSFile.Fields(n - 1).Name
Next

'Transfer the data to Excel.
oSheet.Range("A2").CopyFromRecordset(RSFile)

'Save the workbook and quit Excel.
oBook.SaveAs("C:\Path\Filename.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing

.open (strconnection)
.execute(strquery)
.close

Hope this will help. Cheers!

[freedom]

Hi, I noticed in your code that you have'nt set the correct connection string, and specifyinf a recordset that would call for the records. It may be better if you first try to chnge your connection string as in the format below, then open a recordset but first dclare it as nee ADODB. Take note, add a compnent for ADO I think use the ADO 2.8 dll.

THe int32 here is for double


Dim conn As New ADODB.Connection
Dim RSFile As New ADODB.Recordset
Dim rs As ADODB.Recordset
Dim strCnn As String
Dim strQuery As String
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

strCnn = "Provider=SQLOLEDB.1;Password=password;Persist Security Info=False;User ID=sa;Initial Catalog=DATABASE;Data Source=Your Server"
strQuery = "SELECT * FROM TBL_AA"
RSFile.Open(strQuery, strCnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly)
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient

Thanks. Hope this will help Cheers!!