Excel File Locks

Posted on 2011-10-31
Last Modified: 2012-05-12
I developed an application in Access that also ties into Excel, my Access app gets data from Excel and updates 12 tables, then allows the user to select a date range and it dumps out to 12 excel files, refreshes excel and the excel app then has 12 data connections to those files for doing graphs etc.   I open both Excel and Access and run the access vb and it all works fine but if I rerun it a second time it fails on the export to excel and it says the files are open by another application....So I guess the excel files get locked by excel when I refresh the data link, is there a way to temporarily break that link (lock) so I can update those excel files?
Question by:INSCNOC
    1 Comment
    LVL 29

    Accepted Solution

    You need to ensure in your code that all instances of excel that are opened are killed and all workbooks that were opened are also killed something like

    Dim oApp as Excel.Application
    Dim wb as workbook
    your code

    .. before closing
    Set wb = Nothing
    Set oApp = Nothing


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now