Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I used Ken Snell's site as a reference...



    I've had problems with closing Excel from Access (2000). Sometimes the Excel process would stay running and I would have to open the task manager to close Excel. It seemed to be a timing problem, so I threw in several DoEvents commands.... seems to work.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Excelbtn_Click()
    'requires a reference to DAO 3.6 Object Library
       Dim xlx As Object
       Dim xlw As Object
       Dim xlst As Object
       Dim xlc As Object
    
       Dim blnEXCEL As Boolean
    
       blnEXCEL = False
    
       ' Establish an EXCEL application object
       On Error Resume Next
       Set xlx = GetObject(, "Excel.Application")
       If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True
       End If
       Err.Clear
       On Error GoTo 0
    
       ' Change True to False if you do not want the workbook to be
       ' visible when the code is running
       xlx.Visible = True
    
       ' Replace C:\Filename.xls with the actual path and filename
       ' of the EXCEL file from which you will read the data
       Set xlw = xlx.Workbooks.Open("C:\Filename.xls") 
    
       ' Replace WorksheetName with the actual name of the worksheet
       ' in the EXCEL file
       ' (note that the worksheet must already be in the EXCEL file)
       Set xlst = xlw.Worksheets("WorksheetName")
    
       DoEvents
       
       '--- Format code starts here
    
       Set xlc = xlst.Range("C3:C10")
       xlc.Interior.ColorIndex = 6
       xlc.Interior.Pattern = xlSolid
    
       Set xlc = xlst.Range("A29:K29")
       xlc.Interior.ColorIndex = 6
       xlc.Interior.Pattern = xlSolid
    
       Set xlc = xlst.Range("K2:K47")
       xlc.NumberFormat = "m/d/yyyy"
       xlst.Range("A2").Select
       DoEvents
       
    
       '--- End Format code ----
    
    
       ' Close the EXCEL file, and clean up the EXCEL objects
       xlw.Close True    ' Save and close the EXCEL file
    
       Set xlc = Nothing
       Set xlst = Nothing
       Set xlw = Nothing
       DoEvents
    
       If blnEXCEL = True Then
          xlx.Quit
          DoEvents
       End If
    
       Set xlx = Nothing
    
       MsgBox "Done"  'I like to know when the routine is done
    
    End Sub

  2. #17
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    Thanks Steve!

    I will take a look and parse this code for myself soon. It will be useful to understand all the logic going on here. I like the boolean used to determine whether the application is running or not. I think that could have dealt with some of my problems right there.

    With respect to closing Excel, my Excel macro does that very nicely with the following code

    Code:
    With ActiveWorkbook
         .Save
         .Close
    End With
    
    With Application
         .Quit
    End With
    
    Set xlApp = Nothing
    I know that's not what you were strictly referring to but if you find yourself writing the macro in Excel, that seems to do the trick without any hidden instances of the application hanging around. I hate it when that happens.

  3. #18
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've used essentially the same code in Excel, but using automation to save/close/quit Excel from Access is a different animal. Sometimes the code works and Excel quits; other times Excel will hang around like a stalker.

    If I single step through the code, Excel will quit; run the code full speed and Excel is left still running. So I slowed down the execution by using the DoEvents commands.

    Anyhoo, that was my "All Access" code to control Excel from Access.

  4. #19
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Now if Access could only save in .xlsx format, my users wouldn't have to click a thing after they export the report.
    It looks like you might be able to do that if you use TransferSpreadsheet instead out OutputTo.
    See: http://www.pcreview.co.uk/forums/tra...-t3870322.html

  5. #20
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    Wow, this is great stuff. The Excel exports in my next update are going to so damn snazzy thanks to you guys. I really appreciate the help.

    Thanks!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatting exported CSV file
    By oatsybob in forum Programming
    Replies: 3
    Last Post: 02-04-2013, 11:32 PM
  2. Replies: 5
    Last Post: 02-24-2012, 11:02 AM
  3. to open Exported Excel File
    By waqas in forum Programming
    Replies: 3
    Last Post: 10-16-2011, 12:33 PM
  4. Replies: 6
    Last Post: 08-16-2011, 12:54 PM
  5. Replies: 1
    Last Post: 06-16-2010, 11:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums