Please review Access Code

[jim may]

My first attempt at Automation -- Does anyone see any HOLES in this Code
When I F8 thru it - Excel DOES NOT OPEN for me to see the Final Destination Sheet; Someting is missing; But what?

Option Compare Database
Option Explicit

Sub ExportToMyExcel()
Dim appExcel As New Excel.Application
Dim mySourceBook As Excel.Workbook
Dim myDestinationBook As Excel.Workbook
Dim mySourceSheet As Excel.Worksheet
Dim myDestinationSheet As Excel.Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryDataExportedToExcel", _
         "C:\Users\Jmay\Documents\Causey-Jack\CBF Project\Last_CBF_DB_Export.xlsx", True
On Error GoTo 0

Set myDestinationBook = appExcel.Workbooks.Open("C:\Users\Jmay\Documents\Causey-Jack\Ministers_Churches_Database_4.xlsm")
Set myDestinationSheet = myDestinationBook.Worksheets("Ministers")
Set mySourceBook = appExcel.Workbooks.Open("C:\Users\Jmay\Documents\Causey-Jack\CBF Project\Last_CBF_DB_Export.xlsx")
Set mySourceSheet = mySourceBook.Worksheets("qryDataExportedToExcel")
Set Rng1 = mySourceSheet.UsedRange
Set Rng2 = Rng1.Offset(1, 0).Resize(Rng1.Rows.Count - 1)
Rng2.Copy
myDestinationSheet.Range("A4").PasteSpecial (xlPasteValues)

Set Rng1 = Nothing
Set Rng2 = Nothing
Set myDestinationSheet = Nothing
Set mySourceSheet = Nothing
Set myDestinationBook = Nothing
Set mySourceBook = Nothing
Set appExcel = Nothing
End Sub

 

[Norie]

Jim

The first thing I would recommend, and you probably know what it is, is to lose the On Error... stuff.

Apart from that I don't see anything in the code that is actually making Excel or a workbook visible.

 

[xenou]

On a similar note, if this did error, then you probably don't want to just move along with the code after that ...

On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryDataExportedToExcel", _
         "C:\Users\Jmay\Documents\Causey-Jack\CBF Project\Last_CBF_DB_Export.xlsx", True
On Error GoTo 0

Why the "paranoia" about transferspreadsheet here?
ξ

 

[GeofW]

Yes, something is missing

appExcel.Visible = True

 

[jim may]

I'm ALMOST there thanks to all 3 of you. I've made all your suggested changes.
After completing the Access macro, and I manually close the Ministers_Churches_Database_4.xlsm, which is READ-ONLY, without SAVING. Then if I open Excel fresh and then try to open the myDestinationBook, when it opens i get the messge

Ministers_Churches_Database_4.xlsm is locked for editing.. by Jim May, etc

This file should open without this message. What can be done to correct this?

Jim

 

[Hermanito]

You would be best to add this line somewhere:

MyDestinationBook.Close

before the part where you set all object variables to nothing. Probably the filehandle isn't released properly by Access and it is blocking Excel from opening it...

 

[Norie]

Jim

Why do you think the file should open without that message?

Have you considered just using Access rather than trying to automate things. which seems to be causing problems.?

 

[xenou]

Close both workbooks if they are still open (destinationbook, sourcebook). And finish off with:

appExcel.Quit

Then, as noted, set the objects to nothing. Note: Beware that if the apps are not visible, you want to be sure there are no dialog boxes that may be waiting for responses (i.e., do you want to save this file type of messages). Edit -- unless of course you intend to leave Excel open and available to your end user...

 

[jim may]

Actually, to begin with the user is in Access and on a Minister Form record, On each there I have a cmd button (caption: Analize in Excel) that (at anytime) they can click on to do some quick anaysis. Once complete with the analysis, I have a button on the Ministers Sheet of the workbook Ministers_Churches_Database_4.xlsm (myDestinationBook) which reads "Return to the Database" << where I want them to get back to where they were in Access BEFORE the Export..

Hermanito, -- I would put the .Close in the Excel cmd button and code in Excel behind the "Return to the Database" button. Right?

xenou, -- I added the Source.close into the main Access code - and it is working fine.

Norie, -- It just seems ODD that it opens that way -- it seems like something didn't get "released" in the file system; Can it be AVOIDED?; If so how?

Jim

 

[xenou]

You may have had some ghostly (not visible but still there) excel app open from your previous testing. When you create these Excel apps through automation each one is a separate instance. With all the experimentation you were doing - who knows but there might have been 20 Excels running at the time. You could check Task Manager if it happens again.

ξ