Results 1 to 6 of 6

Thread: Excel file remains opne

  1. #1

    Thread Starter
    Member
    Join Date
    Mar 2020
    Posts
    49

    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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,807

    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.

  4. #4

    Thread Starter
    Member
    Join Date
    Mar 2020
    Posts
    49

    Re: Excel file remains opne

    Quote Originally Posted by westconn1 View Post
    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

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  6. #6

    Thread Starter
    Member
    Join Date
    Mar 2020
    Posts
    49

    Re: Excel file remains opne

    Quote Originally Posted by westconn1 View Post
    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
  •  



Click Here to Expand Forum to Full Width