[AccessD] Excel Not Closing
Rocky Smolin
rockysmolin2 at gmail.com
Tue Apr 11 10:21:35 CDT 2023
I have done that when the formatting was complicated. Or I was pressed for
time. Or just a little lazy.
The downside is that the user has to know where the template is and be
able to locate it using the Open File Dialog.
Doing everything in code became much easier once I learned the trick of
using the macro recorder in Excel, and then just copying out the code that
was generated. Relieved me of knowing the detail of how the Excel object
worked.
The mystery is that the opening and closing of the Excel objects is the
same as the one I used in a different application for the same client. And
that one works.
Access...gotta love it...
r
On Tue, Apr 11, 2023 at 4:38 AM John Colby <jwcolby at gmail.com> wrote:
> Rocky, one thing to think about is to have a formatted spreadsheet already
> saved. Any required pages, any headers, cells formatted with pretty
> colors, cells already set with formulas etc. Then copy the format to a new
> workbook, open it and just push the data. That's how I handled it when I
> was automating excel.
>
> On Mon, Apr 10, 2023 at 8:48 PM Rocky Smolin <rockysmolin2 at gmail.com>
> wrote:
>
> > Ok - Here it is.
> >
> > TIA
> >
> > Rocky
> >
> >
> > ' Object variables for Automation stuff
> > Dim objXLApp As Excel.Application
> > Dim objXLBook As Excel.Workbook
> > Dim objXLWS As Excel.Worksheet
> >
> > and the sub:
> >
> > Private Sub cmdToExcel_Click()
> >
> > strSQL = "SELECT DISTINCTROW tblProperty.fldPropertyName,
> > tblUser.fldUserInitials, tblWho.fldWho, tblWhat.fldWhat,
> > tblLocation.fldLocation, " _
> > & "tblTasks.fldTaskDescription, tblTasks.fldTasksMaintenance,
> > tblTasks.fldTasksAppraisal, tblTasks.fldTasksCustom, " _
> > & "qryTaskSubTaskInProcess.fldTaskSubTaskStepDescription,
> > qryTaskSubTaskInProcess.fldtaskSubTaskUrgencyText " _
> > & "FROM (tblTasks LEFT JOIN tblProperty ON tblTasks.fldPropertyID =
> > tblProperty.fldPropertyID) " _
> > & "LEFT JOIN ((((qryTaskSubTaskInProcess LEFT JOIN tblWho ON
> > qryTaskSubTaskInProcess.fldTaskSubTaskWhoID = tblWho.fldWhoID) " _
> > & "LEFT JOIN tblWhat ON qryTaskSubTaskInProcess.fldTaskSubTaskWhatID
> =
> > tblWhat.fldWhatID) " _
> > & "LEFT JOIN tblLocation ON
> > qryTaskSubTaskInProcess.fldTaskSubTaskLocationID =
> > tblLocation.fldLocationID) " _
> > & "LEFT JOIN tblUser ON
> > qryTaskSubTaskInProcess.fldTaskSubTaskStepPersonID = tblUser.fldUserID)
> ON
> > tblTasks.fldTaskID = qryTaskSubTaskInProcess.fldTaskID "
> >
> >
> > Set db = CurrentDb
> > Set rsTaskExport = db.OpenRecordset(strSQL)
> >
> > strfilter = Me.Filter
> > strfilter = Replace(strfilter, "fldUserInitials",
> > "tblUser.FldUserInitials")
> >
> >
> > If Nz(Me.Filter, "") <> "" Then strSQL = strSQL & " WHERE " & strfilter
> >
> > If Nz(Me.OrderBy, "") <> "" Then strSQL = strSQL & " ORDER BY " &
> > Me.OrderBy
> >
> > strSQL = strSQL & ";"
> >
> > Me.txtSQL = strSQL
> >
> > strFileName = gstrBackEndPath & "\" & "Task_List_" & Format(Now,
> "MM-DD-YY
> > HH_MM_SS") & ".xlsx"
> >
> > ' Create Spreadsheet
> > Set objXLApp = New Excel.Application
> > Set objXLBook = objXLApp.Workbooks.Add
> > Set objXLWS = objXLBook.Sheets(1)
> >
> > intI = 1
> >
> > ' Headings
> > objXLWS.Cells(intI, 1) = "Entity"
> > objXLWS.Cells(intI, 2) = "IP"
> > objXLWS.Cells(intI, 3) = "Who"
> > objXLWS.Cells(intI, 4) = "What"
> > objXLWS.Cells(intI, 5) = "Where"
> > objXLWS.Cells(intI, 6) = "Task Description"
> > objXLWS.Cells(intI, 7) = "First In Process Sub Task Description"
> > objXLWS.Cells(intI, 8) = "First In Process Sub Task Urgency"
> > objXLWS.Cells(intI, 9) = "Maintenance"
> > objXLWS.Cells(intI, 10) = "Appraisal"
> > objXLWS.Cells(intI, 11) = "Custom"
> >
> > intI = intI + 1
> >
> > Do While rsTaskExport.EOF = False
> > Me.txtRowNumber = intI
> > intI = intI + 1
> > objXLWS.Cells(intI, 1) = rsTaskExport!fldPropertyName
> > objXLWS.Cells(intI, 2) = rsTaskExport!fldUserInitials
> > objXLWS.Cells(intI, 3) = rsTaskExport!fldWho
> > objXLWS.Cells(intI, 4) = rsTaskExport!fldWhat
> > objXLWS.Cells(intI, 5) = rsTaskExport!fldLocation
> > objXLWS.Cells(intI, 6) = rsTaskExport!fldTaskDescription
> > objXLWS.Cells(intI, 7) = rsTaskExport!fldTaskSubTaskStepDescription
> > objXLWS.Cells(intI, 8) = rsTaskExport!fldtaskSubTaskUrgencyText
> > objXLWS.Cells(intI, 9) = rsTaskExport!fldTasksMaintenance
> > objXLWS.Cells(intI, 10) = rsTaskExport!fldTasksAppraisal
> > objXLWS.Cells(intI, 11) = rsTaskExport!fldTasksCustom
> >
> > rsTaskExport.MoveNext
> >
> > Loop
> >
> >
> > objXLApp.Cells.Select
> > objXLApp.Cells.EntireColumn.AutoFit
> >
> > With objXLApp.Selection.Font
> > .Name = "Calibri"
> > .Size = 12
> > End With
> >
> > objXLApp.Rows("1:1").Select
> > objXLApp.Selection.Font.FontStyle = "Bold"
> >
> > Set objXLWS = Nothing
> >
> > objXLBook.SaveAs strFileName
> > objXLBook.Close True
> > Set objXLBook = Nothing
> >
> > objXLApp.Quit
> > Set objXLApp = Nothing
> >
> > MsgBox "All Displayed Tasks List Exported to: " & vbCrLf & vbCrLf &
> > strFileName
> >
> > Application.FollowHyperlink strFileName
> >
> > End Sub
> >
> > On Mon, Apr 10, 2023 at 3:10 PM Stuart McLachlan <stuart at lexacorp.com.pg
> >
> > wrote:
> >
> > > Yep, the whole block would be useful.
> > >
> > > A couple of snippets from 12 year old answers I posted to similar
> > > questions;
> > >
> > > .HPageBreaks.Add Before:=Cells(lRow + 1, 6)
> > > There's your unqualified reference. Change it to:
> > > .HPageBreaks.Add Before:=ExcelwBook.sheets(sSheet).Cells(lRow + 1, 6).
> > >
> > > and
> > >
> > > oxlCht.SetSourceData Source:=Sheets("Data").Range("B3:C28"), PlotBy:= _
> > > xlColumns
> > > Sheets need to be qualified. Off the top of my head, try:
> > > oxlCht.SetSourceData Source:=oxlWkb.Sheets("Data").Range("B3:C28"),
> > > PlotBy:= _
> > > xlColumns
> > >
> > >
> > >
> > > On 10 Apr 2023 at 14:59, Rocky Smolin wrote:
> > >
> > > > Stuart:
> > > >
> > > > Not sure what this means:
> > > >
> > > > <quote> When you write code to use an Excel object, method, or
> > > > > property, you should always precede the call with the appropriate
> > > > > object variable. If you do not, Visual Basic establishes its own
> > > > > reference to Excel. </quote>
> > > >
> > > > Am I not doing that?
> > > >
> > > > I could post the whole snip if that would help?
> > > >
> > > > Rocky
> > > >
> > > >
> > > >
> > > >
> > > > On Mon, Apr 10, 2023 at 1:59PM Stuart McLachlan
> > > > <stuart at lexacorp.com.pg> wrote:
> > > >
> > > > > Searching through my archives, it's a long time sionce this one
> came
> > > > > up, but it used to be fairly regular (the last time was in Jun
> > > > > 2014):
> > > > >
> > > > > Here's what I said in 2009:
> > > > >
> > > > > On 14 Nov 2009 at 8:29, Stuart McLachlan wrote:
> > > > >
> > > > > > Third time I've answered this on the list this year.
> > > > > >
> > > > > > See:
> > > > > > TrasnferSpreadsheet failing on 18 March 2009
> > > > > > and
> > > > > > Access to Excel Automation works first time on 13 July 2009:
> > > > > > in the archives.
> > > > > >
> > > > > > The old "unqualified reference" strikes again :-)
> > > > > > See http://support.microsoft.com/kb/319832
> > > > > >
> > > > > > <quote> When you write code to use an Excel object, method, or
> > > > > > property, you should always precede the call with the appropriate
> > > > > > object variable. If you do not, Visual Basic establishes its own
> > > > > > reference to Excel. </quote>
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > On 10 Apr 2023 at 10:55, Rocky Smolin wrote:
> > > > >
> > > > > > Dear List:
> > > > > >
> > > > > > I have an app which exports data to an Excel spreadsheet. It is
> > > > > > not amenable to using TransferSpreadsheet because of formatting
> > > > > > problems, etc. So I use the brute force method and it works well.
> > > > > >
> > > > > > Problem is that at the end of the process the user wants the app
> > > > > > to open the spreadsheet.
> > > > > >
> > > > > > So I use Application.FollowHyperlink strFileName. Problem is that
> > > > > > the spreadsheet opens blank.
> > > > > >
> > > > > > When I go to the folder and open the spreadsheet it's there, all
> > > > > > perfect. So I put a breakpoint in at the statement
> > > > > > Application.FollowHyperlink strFileName and looked at the Task
> > > > > > Manager and sure enough there was an instance of Microsoft Excel
> > > > > > still there in the list of background processes.
> > > > > >
> > > > > > If I end that Excel process and then let the app execute the
> > > > > > Follow Hyperlink it opens just fine. So it's that leftover
> > > > > > instance of Excel that's hosing up the works.
> > > > > >
> > > > > > I set the objects like so:
> > > > > >
> > > > > > Set objXLApp = New Excel.Application
> > > > > > Set objXLBook = objXLApp.Workbooks.Add
> > > > > > Set objXLWS = objXLBook.Sheets(1)
> > > > > >
> > > > > > and at the end close up like so:
> > > > > >
> > > > > > objXLBook.SaveAs strFileName
> > > > > > objXLBook.Close True
> > > > > >
> > > > > > Set objXLWS = Nothing
> > > > > > Set objXLBook = Nothing
> > > > > >
> > > > > > objXLApp.Quit
> > > > > > Set objXLApp = Nothing
> > > > > >
> > > > > > How do I get rid of that instance of Excel in the background
> > > > > > processes?
> > > > > >
> > > > > >
> > > > > > MTIA
> > > > > >
> > > > > > Rocky
> > > > > > --
> > > > > > AccessD mailing list
> > > > > > AccessD at databaseadvisors.com
> > > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > > Website: http://www.databaseadvisors.com
> > > > > >
> > > > >
> > > > >
> > > > > --
> > > > > AccessD mailing list
> > > > > AccessD at databaseadvisors.com
> > > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > > Website: http://www.databaseadvisors.com
> > > > >
> > > > --
> > > > AccessD mailing list
> > > > AccessD at databaseadvisors.com
> > > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > > Website: http://www.databaseadvisors.com
> > >
> > >
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> John W. Colby
> Colby Consulting
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
More information about the AccessD
mailing list