Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Posts
    7

    Unanswered: OPEN and CLOSE SECOND EXCEL FILE

    I have an existing open excel workbook1.xlsx that must remain open and unaffected. On the click of a button ,using VB code,I need to open a second workbook2.xlsx. On the click of a separate button I also need to close that same file workbook2.xlsx and eliminate the excel instance running in task manager. The problem Im having is the code Ive used so far either throws error messages, opens then closes the requested spreadsheet, or just does nothing. I think its because workbook1.xlsx is open.

    Thanks in advance for any insight you can provide

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ATECHNICAL2 View Post
    ... The problem Im having is the code Ive used so far either throws error messages, opens then closes the requested spreadsheet, or just does nothing...
    What's this code?
    Have a nice day!

  3. #3
    Join Date
    Nov 2006
    Posts
    7
    One of the things I've tried is as follows.It creates an instance of Excel running in the Task Manager, but it never shows the spreadsheet.
    If I close the other spreadsheet that is already open, and run the same code it works fine.

    Public Function openLIMITORDERSPRICINGOLD()
    Dim oApp As Object
    Set oApp = GetObject("y:\limitorderspricing.xlsx")

    With oApp

    .Application.Visible = True
    .Parent.Windows(1).Visible = True

    End With
    End Function

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:

    1. Create a reference in your project to the Excel xx Object Library, where xx depends on the version of Office (11.0 for Office 2003).

    2. Use a code similar to this:
    Code:
    Sub UseRemoteExcelObject()
    '
    ' This needs a reference to Excel (here for Office 2003):
    ' Tools --> References --> Microsoft Excel 11.0 Object Library.
    '
        Dim xlapp As Excel.Application
        Set xlapp = New Excel.Application
    '
    ' It's also possible to use Late Binding.
    ' (This does not need a reference to Excel but is less efficient).
    '
    '    Dim xlApp As Object
    '    xlapp = CreateObject("Excel.Application")
    
        With xlapp
        
        ' Useful when you're debugging.
        '
            .Visible = True
        
        ' Open the Excel file you want to work with.
        '
            .Workbooks.Open "C:\Custom Error Codes.xls"
    '
    ' Do something with the open workbook.
    '
    '    ' Select the proper sheet.
    '    '
    '        .Sheets("Sheet2").Select
    '
    '    ' Select the upper-left cell (A1).
    '    '
    '        .Range("A1").Select
    '
    '    ' Write something into it.
    '    '
    '        .Selection = "Something else"
    '
    ' End of process.
    '
        ' Save the file (if necessary).
        '
            .ActiveWorkbook.Save
        
        ' Then close it.
        '
            .Workbooks.Close
        
        ' Close Excel. !!! This is very important !!!
        ' If you do not property close the instance of Excel it will remain
        ' locked in memory with no visible interface (only the Task Manager to kill it).
        '
            .Quit
        End With
        
        ' Clean up
        '
        Set xlapp = Nothing
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Nov 2006
    Posts
    7
    looks promising I haven't tested it yet but when I open the spreadsheet I need to leave it open until the user has a need to close it, which will probably be at least a few hours after it was opened.I want to link the separate closing code to a button on a form.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always declare the object variable as global in the declarations section of an independant module:
    Code:
    Global xlapp As Excel.Application
    But I would not recommend such a solution. I would create a form (it can be invisible-hidden) that would instanciate the object variable (declared public or accessible through properties and methods) and that would force closing the instance of Excel when it is closed. In the class module of the form:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_xlapp As Excel.Application
    
    Private Sub Form_Close()
    
        If Not m_xlapp Is Nothing Then
            m_xlapp.Quit
            Set m_xlapp = Nothing
        End If
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Set m_xlapp = New Excel.Application
        
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Nov 2006
    Posts
    7
    how would I link that code back to the specific spreadsheet that I want to open and then subsequently close?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The form here is just a container for an instance of Excel. The advantage of this methos is that Access already provides the necessary mechanisms to address the form (through the Forms collection), to care for executing the Form_Close event when the form is closed, etc.

    As I formerly wrote, you can add public properties and methods to the form to perform various operations on the instance of Excel it contains. Here are some very trivial examples (all procedures are in the class module of the form which is named Frm_Excel)
    Code:
    Public Function OpenWorkBook(ByVal FileName As String) As Excel.WorkBook
    '
    ' Public method to open a workbook (Excel file).
    '
    ' Use:  Dim XlWbk as Excel.WorkBook
    '       Set XlWbk = Forms("Frm_Excel").OpenWorkBook("MyWorkbook.xls")
    '
    ' or:   Forms("Frm_Excel").OpenWorkBook "MyWorkbook.xls"
    '
        If Len(Dir(FileName)) > 0 Then  ' File exist --> open it.
            Set OpenWorkBook = m_xlapp.Workbooks.Open(FileName)
        Else
            Set OpenWorkBook = m_xlapp.Workbooks.Add
        End If
        
    End Function
    
    Public Function GetWorkBook() As Excel.WorkBook
    '
    ' Public function (can be seen as a get-only property) to retrieve a handle to the active workbook.
    '
    ' Use:  Dim XlWbk as Excel.WorkBook
    '       Set XlWbk = Forms("Frm_Excel").GetWorkBook
    '
        If Not m_xlapp Is Nothing Then
            If Not m_xlapp.ActiveWorkbook Is Nothing Then Set GetWorkBook = m_xlapp.ActiveWorkbook
        End If
        
    End Function
    
    Public Sub SaveWorkBook(Optional ByVal FileName As String)
    '
    ' Public method to save the active workbook (open Excel file).
    '
    ' Use:  Forms("Frm_Excel").SaveWorkBook
    '
    ' or:   Forms("Frm_Excel").SaveWorkBook "NewFileName.xls"
    '
        If Len(FileName) > 0 Then  ' New Name.
            m_xlapp.ActiveWorkbook.SaveAs FileName
        Else
            m_xlapp.ActiveWorkbook.Save
        End If
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Nov 2006
    Posts
    7
    Thanks for the response sorry for the delay in my response. I was out of the office last week and now I need to leave and shovel water from my basement left by the lovely Hurricane Irene. I will hopefully have time to look at your solution tomorrow. I hope you can wait until then but I surely understand if you don't as it has been a long time.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, you're welcome.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •