Create excel file when another excel file are open.

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?
   
DCRAPACCESSAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
rockiroadsCommented:
Using GetObject will try to find the existing excel instance. Why dont you just create a new excel object instead?
0
 
tbsgadiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.