-
Aug 11th, 2020, 01:43 AM
#1
Thread Starter
Member
Excel file remains opne
Dear friends,
I have developed application with VB6. I wish to create excel file using template excel file. My programme works fine when I generate report first time. But when I close excel file and wish to generate new reoprt it is not working. Blank excel application opens without any sheet. When I try to open excel template it shows "File locked by user" and open as read only. Even after closing excel application it remains open somewhere. I used task bar to see if it is open the also it donot show open application. Only when I try to shut down message come " Do you want to save excel file?". That means it remains open and hidden. If anyone has solution to this problem please help me.
Following is the code to open and write excel template.
Code:
Sub excelpathology()
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
'On Error Resume Next
If TextBox14.Text = "" Or TextBox15.Text = "" Then
MsgBox "Please entre start date and end date", vbExclamation, "Blank date"
Exit Sub
End If
If CDate(TextBox14.Text) > CDate(TextBox15.Text) Or CDate(TextBox15.Text) > Date Then
MsgBox "Please entre proper date", vbExclamation, "Invalid date"
Exit Sub
End If
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String
Dim i, j As Long
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "\\192.168.1.120\d\d\Database.accdb"
qry = "SELECT Sr,[Reg No], [Patient Name], Age, Sex, Date, Username, [Pathology], [P Amount], [P Discount] FROM BDaily WHERE Date >= " & Format$(CDate(TextBox14.Text), FORMAT_SQL_DATE) & " AND Date < " & Format$(CDate(TextBox15.Text) + 1, FORMAT_SQL_DATE) & " AND len(Pathology) > 2 "
'qry = "SELECT * FROM BDaily WHERE Date >= " & Format$(CDate(TextBox14.Text), FORMAT_SQL_DATE) & " AND Date < " & Format$(CDate(TextBox15.Text) + 1, FORMAT_SQL_DATE) & " AND len(Pathology) > 2 "
'Reg No, Patient Name, Physiotherapy, Phy Amount, Phy Discount
rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
If rst.RecordCount = 0 Then
MsgBox "No record between selected date" & vbNewLine & vbNewLine & "Please select different date", vbExclamation, "No record"
Exit Sub
Else
objExcel.Workbooks.Open "\\192.168.1.120\d\d\Backupp.xlsx"
objExcel.Worksheets("Sheet1").Activate
objExcel.Range("A1") = "Pathology reports from " & TextBox14.Text & " to " & TextBox15.Text
objExcel.Range("A3").CopyFromRecordset rst
Application.Visible = True
End If
rst.Close
cnn.Close
End Sub
-
Aug 11th, 2020, 03:56 AM
#2
Re: Excel file remains opne
of course you are not closing the workbook or the excel appication
also you are not saving any updated data from the recordset to the workbook
with the current code the data will always wholly or partially overwrite the previous recordset data
as i am unsure what you actually want to achieve hard to suggestions
better to avoid activating the sheet, just use fully qualified ranges
Code:
set wb = objExcel.Workbooks.Open("\\192.168.1.120\d\d\Backupp.xlsx")
with wb.Worksheets("Sheet1")
.Range("A1") = "Pathology reports from " & TextBox14.Text & " to " & TextBox15.Text
.Range("A3").CopyFromRecordset rst
End With
objExcel.Visible = True ' using application here may well cause a problem and should be avoided
the workbook is left open here, so unless it is closed by the user, it will remain open and give the error you are encountering
if you want to save and close the workbook and excel
Code:
wb.close true 'true to save changes
objExcel.quit
if you want a new workbook each time the code is run, then you should have an excel template file and open a new workbook (objExcel.workbooks.add) based on the specific template
if you are going to create multiple workbooks, you should leave the objExcel in scope so excel only has to be started once, you could close the previous workbook, but leave the application open
if you want to add new information after the existing data you need to find the last row used in the appropriate column, you could leave the workbook open then keep adding data to the worksheet
as is said, too many choices and not enough information
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 11th, 2020, 04:03 AM
#3
Re: Excel file remains opne
I am not entirely sure what is going on but:
To create an object reference to an open workbook using its file path and then close it:
Code:
Dim wb As Excel.Workbook
Set wb = GetObject(path)
wb.Close SaveChanges:=false|true
Set wb = Nothing
And to close Excel:
Code:
Dim ex As Excel.Application
'Some code interacting with Excel.
ex.Quit
Set ex = Nothing
To prevent null reference errors you might want to put your code in an If/EndIf block:
Code:
If Not objvar Is Nothing Then
'Your code.
End If
Hope this helps.
-
Aug 11th, 2020, 06:40 AM
#4
Thread Starter
Member
Re: Excel file remains opne
Originally Posted by westconn1
of course you are not closing the workbook or the excel appication
also you are not saving any updated data from the recordset to the workbook
with the current code the data will always wholly or partially overwrite the previous recordset data
as i am unsure what you actually want to achieve hard to suggestions
better to avoid activating the sheet, just use fully qualified ranges
Code:
set wb = objExcel.Workbooks.Open("\\192.168.1.120\d\d\Backupp.xlsx")
with wb.Worksheets("Sheet1")
.Range("A1") = "Pathology reports from " & TextBox14.Text & " to " & TextBox15.Text
.Range("A3").CopyFromRecordset rst
End With
objExcel.Visible = True ' using application here may well cause a problem and should be avoided
the workbook is left open here, so unless it is closed by the user, it will remain open and give the error you are encountering
if you want to save and close the workbook and excel
Code:
wb.close true 'true to save changes
objExcel.quit
if you want a new workbook each time the code is run, then you should have an excel template file and open a new workbook (objExcel.workbooks.add) based on the specific template
if you are going to create multiple workbooks, you should leave the objExcel in scope so excel only has to be started once, you could close the previous workbook, but leave the application open
if you want to add new information after the existing data you need to find the last row used in the appropriate column, you could leave the workbook open then keep adding data to the worksheet
as is said, too many choices and not enough information
Thanks for quick reply. My perpose is to open template excel file, write data in it and make it visible to user so that it can be used. It works fine on first time. If user wants to save it I use saveas option in excel and then close original template without saving changes so that template remains same every time. I dont know what is wrong but even after closing excel file it remains open somewhere. I donot want the programme to save and close the file just write and make visible so that user can further use it as he wants. Please give suggestion. Thanks
-
Aug 11th, 2020, 07:16 AM
#5
Re: Excel file remains opne
if the user has not closed either the file or excel, you will get the error
you should use a template file with .xltx extention, then open a new file each time based on that template, as each time the code runs a new file will be created, not reusing the same workbook as previous
as suggested above by peter swinkels you can use getobject to reattach to the file that is already open, but i don't think that is what you want to do
each time you run this code a new instance of excel will be created, this should be avoided, so all workbooks are opened in the same instance of excel
declare your excel object variable in the general section at the top of the code pane, instead of in the procedure
change the code in the procedure to only open a new instance of excel if the previous one has been closed (or no previous one)
Code:
' Dim objExcel As Excel.Application ' move this line to the general section at top of code pane
If objExcel Is Nothing Then Set objExcel = New Excel.Application
Code:
set wb = objExcel.Workbooks.Add("\\192.168.1.120\d\d\Backupp.xlst")
each time the code is run you will get Book1, Book2 etc
the user can then have multiple workbooks open to inspect later
the original code calling the application object of excel instead of your excel variable or activating the worksheet may well be causing a problem
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Aug 11th, 2020, 08:04 AM
#6
Thread Starter
Member
Re: Excel file remains opne
Originally Posted by westconn1
if the user has not closed either the file or excel, you will get the error
you should use a template file with .xltx extention, then open a new file each time based on that template, as each time the code runs a new file will be created, not reusing the same workbook as previous
as suggested above by peter swinkels you can use getobject to reattach to the file that is already open, but i don't think that is what you want to do
each time you run this code a new instance of excel will be created, this should be avoided, so all workbooks are opened in the same instance of excel
declare your excel object variable in the general section at the top of the code pane, instead of in the procedure
change the code in the procedure to only open a new instance of excel if the previous one has been closed (or no previous one)
Code:
' Dim objExcel As Excel.Application ' move this line to the general section at top of code pane
If objExcel Is Nothing Then Set objExcel = New Excel.Application
Code:
set wb = objExcel.Workbooks.Add("\\192.168.1.120\d\d\Backupp.xlst")
each time the code is run you will get Book1, Book2 etc
the user can then have multiple workbooks open to inspect later
the original code calling the application object of excel instead of your excel variable or activating the worksheet may well be causing a problem
Fantastic!
This worked very well and now no problem. It is very smooth and fast. Thanks a lot for your kind help. I was troubled since many days. Once again thanks.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|