i export records from tables into excel files thru vb6.
the code used is
*********************
Dim rs1 As ADODB.Recordset
Dim objFiled As ADODB.Field
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim intSheetCounter As Integer
Dim intfieldcounter As Integer
Dim strHandler As String
'On Error GoTo Command1_Click_Error
'Open an Excel application an set it to a work book workbook.
Set rs1 = New ADODB.Recordset
Set objApp = New Excel.Application
Set objBook = objApp.Workbooks.Add
'Open the recordset will return multiple
rs1.Open "select * from table1", conn
intSheetCounter = 0
While Not rs1 Is Nothing
'If redcordset has records then populate spreadsheet
If Not rs1.EOF Then
intSheetCounter = intSheetCounter + 1
'After the third sheet add extra sheets
If intSheetCounter > 3 Then
objBook.Worksheets.Add , objSheet
End If 'ntSheetCounter > 3
'set a sheetobject to the current worksheet so it can be renamed
Set objSheet = objBook.Worksheets(intSheetCounter)
objSheet.Name = "myname"
'reset for each new sheet
intfieldcounter = 1
'Set up column headers
For Each objFiled In rs1.Fields
With objSheet.Cells(1, intfieldcounter)
.Value = objFiled.Name
.Font.Bold = True
.Font.Size = 11
.Interior.Color = &H808080
End With 'objSheet.Cells(1, intFieldCounter)
intfieldcounter = intfieldcounter + 1
Next 'objFiled
'Export the recordset to the worksheet
objSheet.Range("a2").CopyFromRecordset rs1
objSheet.Columns.AutoFit
End If 'Not recExport.EOF
'Move to the next worksheet
Set rs1 = rs1.NextRecordset
Wend 'Not rs1 Is Nothing
'objBook.SaveAs "c:\myname\myname.xls"
'Save the Work book
objBook.Close False
objApp.Quit
*************************************8
with this code, if an excel file with same name exists in the folder, it asks whether i want to overwrite it.
instead of this, i want to see whether this excel already exists and if it does, i want to delete it and then create it fresh or don't want the program to ask me for confirmation but just overwrite without asking
besides, if any excel file is open, then it gives message that the excel file to create is not accessible
please can anybody help me to overcome this?
the code used is
*********************
Dim rs1 As ADODB.Recordset
Dim objFiled As ADODB.Field
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim intSheetCounter As Integer
Dim intfieldcounter As Integer
Dim strHandler As String
'On Error GoTo Command1_Click_Error
'Open an Excel application an set it to a work book workbook.
Set rs1 = New ADODB.Recordset
Set objApp = New Excel.Application
Set objBook = objApp.Workbooks.Add
'Open the recordset will return multiple
rs1.Open "select * from table1", conn
intSheetCounter = 0
While Not rs1 Is Nothing
'If redcordset has records then populate spreadsheet
If Not rs1.EOF Then
intSheetCounter = intSheetCounter + 1
'After the third sheet add extra sheets
If intSheetCounter > 3 Then
objBook.Worksheets.Add , objSheet
End If 'ntSheetCounter > 3
'set a sheetobject to the current worksheet so it can be renamed
Set objSheet = objBook.Worksheets(intSheetCounter)
objSheet.Name = "myname"
'reset for each new sheet
intfieldcounter = 1
'Set up column headers
For Each objFiled In rs1.Fields
With objSheet.Cells(1, intfieldcounter)
.Value = objFiled.Name
.Font.Bold = True
.Font.Size = 11
.Interior.Color = &H808080
End With 'objSheet.Cells(1, intFieldCounter)
intfieldcounter = intfieldcounter + 1
Next 'objFiled
'Export the recordset to the worksheet
objSheet.Range("a2").CopyFromRecordset rs1
objSheet.Columns.AutoFit
End If 'Not recExport.EOF
'Move to the next worksheet
Set rs1 = rs1.NextRecordset
Wend 'Not rs1 Is Nothing
'objBook.SaveAs "c:\myname\myname.xls"
'Save the Work book
objBook.Close False
objApp.Quit
*************************************8
with this code, if an excel file with same name exists in the folder, it asks whether i want to overwrite it.
instead of this, i want to see whether this excel already exists and if it does, i want to delete it and then create it fresh or don't want the program to ask me for confirmation but just overwrite without asking
besides, if any excel file is open, then it gives message that the excel file to create is not accessible
please can anybody help me to overcome this?
