Create excel file when another excel file are open.

Posted on 2008-11-10
Last Modified: 2012-06-27
Hi Experts

I'm using this command to create an excel file:

DoCmd.DeleteObject acQuery, varQuery
Set varQryDef = CurrentDb.CreateQueryDef(varQuery, varSQL)

DoCmd.OpenQuery varQuery, acViewNormal, acReadOnly
If DCount("*", varQuery) > 0 Then
    DoCmd.RunCommand acCmdOutputToExcel
    varExcelCreated = True
End If

DoCmd.Close acQuery, varQuery

Set myXL = GetObject(, "Excel.Application")
Set xlBook = myXL.ActiveWorkbook
Set xlSheet = xlBook.Worksheets(1)

Is it a problem if another instance of excel are open while doing this?
    LVL 65

    Expert Comment

    Using GetObject will try to find the existing excel instance. Why dont you just create a new excel object instead?
    LVL 65

    Accepted Solution

    Also, you could just specify a file as    

    Set myXL = CreateObject("Excel.Application")
    Set xlBook = objExcel.Workbooks.Open(FileName:="c:\myfile.xls")

    Or you can create a new excel workbook and paste the query results in there


        Dim objRecSet As DAO.Recordset
        Dim objExcel As Excel.Workbook
        Dim sSql As String
        sSql = "my query goes here - kinda like your varSQL"

        'Assign query results to a recordset    
        Set objRecSet = CurrentDb.OpenRecordset(sSql)

        'Open exce
        Set objExcel = CreateObject("Excel.Application")
        'On sheet 1, from A1, paste the results of the query
        objExcel.Sheets("1").Range("A1").CopyFromRecordset objRecSet

         'Save and get outta here
        Set objExcel = Nothing
        Set objRecSet = Nothing

    LVL 46

    Expert Comment


    I always prefer to use  DoCmd.OutputTo acOutputQuery, not opening the Excel .
    and then to use Set myXL = New Excel.Application etc & open the Excel.

    Good Luck!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now