Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Create excel file when another excel file are open.

Posted on 2008-11-10
3
Medium Priority
?
321 Views
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?
   
0
Comment
Question by:DCRAPACCESS
  • 2
3 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 22920835
Using GetObject will try to find the existing excel instance. Why dont you just create a new excel object instead?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 22920851
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

eg

    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
    objExcel.Save
    objExcel.Close
   
    Set objExcel = Nothing
    Set objRecSet = Nothing

0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 22920867
Hi DCRAPACCESS,

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!

Gary
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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