|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 3rd, 2003, 04:27 PM
|
|||
|
|||
|
Why no sign?
Hi all, I am doing an application which export data to Excel file. Everything seems fine ... even when I close the whole VB or exit the application. However, when I shut down my computer, it will ask me "Do you want to save 'Book1'?", "Do you want to save 'Book2'?","Do you want to save 'Book3'"?, etc .... I don't know where I did wrong as I did quit the excel application and etc. Please take a look and kindly let me know my mistake.
============================================= Code:
Public Sub getExcelByBank()
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim getReport As String
Dim excelFileName As String
Dim xlsApp As Excel.Application
Dim newWorkBook As Excel.Workbook
Dim newWorkSheet As Excel.Worksheet
Set cn = New ADODB.Connection
Set xlsApp = CreateObject("Excel.application")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "\account_db.mdb"
Set xlsApp = New Excel.Application
If xlsApp Is Nothing Then
MsgBox "Cannot Open Excel Application"
Exit Sub
End If
excelFileName = App.Path & "\Report_Summary_ByBank.xls"
'Delete summary report for updated summary report
If Dir(excelFileName) <> "" Then
Kill (excelFileName)
End If
Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add
With newWorkSheet
.Range(.Cells(1, 1), .Cells(3, 8)).Select
xlsApp.Selection.Columns.AutoFit
.Range(.Cells(1, 1), .Cells(3, 8)).RowHeight = 20
.Rows(1).Font.Bold = True
.Rows(2).Font.Bold = True
.Rows(3).Font.Bold = True
.Cells(1, 4).Value = "Andy Tey"
.Cells(2, 4).Value = "Financial Report Summary"
.Cells(3, 4).Value = "****************************************************************"
.Rows(1).HorizontalAlignment = xlHAlignCenter
.Rows(2).HorizontalAlignment = xlHAlignCenter
.Rows(3).HorizontalAlignment = xlHAlignCenter
Dim p As Integer
Dim q As Integer
Dim found As Integer
Dim uniqueBank As Integer
Dim getTT As String
Dim strRow As Integer
Dim RowNumber As Integer
RowNumber = 5
strRow = 4
getReport = "SELECT DISTINCT BANK_NAME FROM BANK_ACCOUNT"
Set rs = cn.Execute(getReport)
Do While Not rs.EOF
Dim rs2 As New ADODB.Recordset
Dim getReport2 As String
getReport2 = "SELECT * FROM REPORT WHERE BANK_NAME='" & rs.Fields("bank_name") & "' ORDER BY BANK_NAME"
Set rs2 = cn.Execute(getReport2)
If (strRow > 4) Then
.Cells(strRow - 1, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(strRow - 1, 1).Value = "Bank Name"
.Cells(strRow - 1, 3).Value = "Bank Type"
.Cells(strRow - 1, 4).Value = "Amount $"
.Cells(strRow - 1, 5).Value = "Deposit / Withdrawal"
.Cells(strRow - 1, 6).Value = "Category"
.Cells(strRow - 1, 7).Value = "Comments"
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 7)).Interior.Color = RGB(180, 180, 180)
'Header settings
.Rows(strRow - 1).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow - 1).Font.Bold = True ' set first row - header as font bold
.Rows(strRow - 1).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 1)).RowHeight = 30
Do While Not rs2.EOF
'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
Range("A" & strRow) = rs2.Fields("bank_name")
Range("C" & strRow) = rs2.Fields("account_type")
Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
Range("F" & strRow) = rs2.Fields("category")
Range("G" & strRow) = rs2.Fields("description")
'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit
rs2.MoveNext
RowNumber = RowNumber + 1
Loop
'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber
Else
.Cells(strRow, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
.Cells(strRow, 1).Value = "Bank Name"
.Cells(strRow, 3).Value = "Bank Type"
.Cells(strRow, 4).Value = "Amount $"
.Cells(strRow, 5).Value = "Deposit / Withdrawal"
.Cells(strRow, 6).Value = "Category"
.Cells(strRow, 7).Value = "Comments"
.Range(.Cells(strRow, 1), .Cells(strRow, 7)).Interior.Color = RGB(180, 180, 180)
'Header settings
.Rows(strRow).HorizontalAlignment = xlHAlignCenter ' set header in center
.Rows(strRow).Font.Bold = True ' set first row - header as font bold
.Rows(strRow).Font.ColorIndex = 5 ' set header words in blue
.Range(.Cells(strRow, 1), .Cells(strRow, 1)).RowHeight = 30
Do While Not rs2.EOF
'set the string equal to the row number to start on
strRow = "" & RowNumber & ""
Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
Range("A" & strRow) = rs2.Fields("bank_name")
Range("C" & strRow) = rs2.Fields("account_type")
Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
Range("F" & strRow) = rs2.Fields("category")
Range("G" & strRow) = rs2.Fields("description")
'auto fit columns
.Range(.Cells(4, 1), .Cells(strRow, 8)).Select
xlsApp.Selection.Columns.AutoFit
rs2.MoveNext
RowNumber = RowNumber + 1
Loop
'set 2 blank rows between new banks
RowNumber = RowNumber + 3
strRow = RowNumber
End If
rs.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
rs.Close
Set rs = Nothing
End With
newWorkSheet.SaveAs App.Path & "\Report_Summary_ByBank.xls"
xlsApp.Visible = True 'Show the Excel file - Report_Summary.xls
'after the excel closed, close the excel application
If xlsApp.Visible = False Then
xlsApp.Quit
End If
Set newWorkSheet = Nothing
Set xlsApp = Nothing
cn.Close
Set cn = Nothing
End Sub
Last edited by Onslaught : October 3rd, 2003 at 10:35 PM.
|
|
#2
October 3rd, 2003, 07:12 PM
|
|||
|
|||
|
I think you need to use the xlsApp.ActiveWindow.close before you quit. This is just a guess though, I haven't done any VB-Excel programming in a while.
|
|
#3
October 6th, 2003, 06:18 AM
|
|||
|
|||
May i comment
May be this helps...
U have this three Declaration... Code:
Dim xlsApp As Excel.Application Dim newWorkBook As Excel.Workbook Dim newWorkSheet As Excel.Worksheet First U Create an Excel Object = xlsApp But U Created The Workbook with add on the WorkSheet Object, Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add U Should at at least Set the WorkBook Object which U can manipulate later. The problem is cause by the WorkBook which is not closed or save because of the newWorksheet that has taken over the new Workbook. Code:
Set newWorkBook = xlsApp.Workbooks.Add Set newWorkSheet = newWorkBook.ActiveSheet .......... .......... ............ newWorkBook.SaveAs ..... 'Do Not Use Quit if U intend To Close It Manually? Set newWorkSheet = Nothing Set NewWorkBook = Nothing Set xlsApp = Nothing
__________________
I May Have Misinterpret U'r Post Correct Me If I Am Wrong......// Enjoy Coding........................../// zak2zak
|
|
#4
October 6th, 2003, 10:26 AM
|
|||
|
|||
|
Thanks Zak ... I think it works. As I check the task manager and teh EXCEL.EXE is not in there after I close the application
 BTW, I am wondering, is it work book and work sheet always need to be declare? Can't we just declare work sheet or just work book? BTW, is work book's extension as same as worksheet? say if I save both work book and work sheet, what extension i should put?? I tried save workbook to "book.xls" and worksheet to "report.xls" ... but I am not sure the purpose of saving two files using the same extension ... please kindly let me know. Thank you very much.
|
|
#5
October 6th, 2003, 11:13 AM
|
|||
|
|||
|
May I Comment
May Be This Helps..
U can just declare WorkBook but to manipulate the WorkSheet property it is advisable to declare WorkSheet. U cannot Use newWorkBook.WorkSheet(1).? but u can use newWorkSheet.something?
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Why no sign? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
