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

 

[jim may]

Yeah, In the immedaie window I would test for any open workbooks:
? Workbooks.Count = 2
? Workbooks(1).name, etc
then would do:
workbooks.close
then
? Workbooks.Count = 0

 

[xenou]

Yeah, In the immedaie window I would test for any open workbooks:
? Workbooks.Count = 2
? Workbooks(1).name, etc
then would do:
workbooks.close
then
? Workbooks.Count = 0

almost - but this would only "see" the workbooks for the current application. If there were another excel instance running, it would have its own workbooks open, unknown to the other. It's hard to say but another instance would certainly be possible to have been fired up during your testing, and it might still be (have been) using that file - just as two people can open the same excel file from their PC's, but the second one only gets a read-only version.

 

[jim may]

thanks xenou, The Task manager has become my friend - can quicky access it from Tasl Bar as I Step through the code. I will post final code today, sometimes. Tks Again!! Jim