Just reveiwing some code..

[jim may]

I'm just reveiwing some code in A Standard ACCESS Module, as follows...
and I'm unclear as to:

1) appExcel.Visible = True << What is this doing?
2) Set myDestinationBook = appExcel.Workbooks.Open << I suppose this opens the File referred to in the path which follows (below). Does this Assume that Excel is not previously Open and Running and It Loads it with this one instance? What if it were already Oen before running this? Would there be a problem with the code..?


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\James\Documents\Trusted_Locations\CBF\Last_CBF_DB_Export.xlsx", True

appExcel.Visible = True
'appExcel.ScreenUpdating = False
Set myDestinationBook = appExcel.Workbooks.Open(FileName:="C:\Users\James\Documents\Trusted_Locations\CBF\Ministers_Churches_Database_4.xlsm", ReadOnly:=True)

 

[boblarson]

1) appExcel.Visible = True << What is this doing?

When you instantiate a new instance of Excel, it is instantiated with the .Visible property set to FALSE, so you can't see it. So in order to see it you have to set the visible property to True.

2) Set myDestinationBook = appExcel.Workbooks.Open << I suppose this opens the File referred to in the path which follows (below). Does this Assume that Excel is not previously Open and Running and It Loads it with this one instance? What if it were already Oen before running this? Would there be a problem with the code..?

It is instantiating the workbook object that has been declared. appExcel has already opened up a new instance of Excel but the workbook object needs to be instantiated as well. So this code instantiates it (you can tell when something is being instantiated because you will see the SET keyword) and then it sets itself to be the workbook that has the file name and opening characteristics as provided in the next part of the code (which goes with the Workbooks.Open part ).

If Excel isn't previously running the
Dim appExcel As New Excel.Application

takes care of that.

If the workbook is already opened when this code runs it could have a problem but it would likely open as read only since another user (or yourself) already has it open.

Hopefully that helps.

 

[jim may]

Actually, the remainder of the code (omitted in Post1) is (FWIW):


Set myDestinationSheet = myDestinationBook.Worksheets("Ministers")
With myDestinationSheet
.Range("A4:Q5000").ClearContents
End With
Set mySourceBook = appExcel.Workbooks.Open("C:\Users\James\Documents\Trusted_Locations\CBF\Last_CBF_DB_Export.xlsx")

Set mySourceSheet = mySourceBook.Worksheets(1)
Set Rng1 = mySourceSheet.UsedRange
Set Rng2 = Rng1.Offset(1, 0).Resize(Rng1.Rows.Count - 1)
Rng2.Copy
myDestinationSheet.Range("A4").PasteSpecial (xlPasteValues)
mySourceBook.Close SaveChanges:=False

With myDestinationSheet
.Range("E1").Interior.ColorIndex = 6
.Range("G1").Interior.ColorIndex = 6
.Range("D4").Select
End With
appExcel.ScreenUpdating = True
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]

Thanks Bob, appreciate you comments. Do you see anything drasticly wrong with the remainder of the code (except I now see a Screenupdating = True, without a previous Scrennupdating = False...

Thanks Again,

Jim

 

[boblarson]

Setting the worksheet and workbook variables to nothing isn't really necessary as they will be destroyed when they go out of scope when the appExcel is set to nothing.

Also there is this up top:

With myDestinationSheet
.Range("A4:Q5000").ClearContents
End With

but if you remove the End With and then just leave that down at the bottom where it is now again, you can simplify this one:

myDestinationSheet.Range("A4").PasteSpecial (xlPasteValues)

to this:

   .Range("A4").PasteSpecial (xlPasteValues)

As long as you also remove the

With myDestinationSheet

which falls just before this part:

Set Rng1 = Nothing
Set Rng2 = Nothing

And personally I would change from Early Binding (where a reference is set and different code is used to instantiate the objects) to Late Binding so it doesn't matter which version of Excel you are using.

So, revisions like this:

Sub ExportToMyExcel()
    Dim appExcel As Object
    Dim mySourceBook As Object
    Dim myDestinationBook As Object
    Dim mySourceSheet As Object
    Dim myDestinationSheet As Object
    Dim Rng1 As Object
    Dim Rng2 As Object
 
    On Error GoTo Errors
 
    Set appExcel = CreateObject("Excel.Application")
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryDataExportedToExcel", _
                              "C:\Users\James\Documents\Trusted_Locations\CBF\Last_CBF_DB_Export.xlsx", True
 
    'appExcel.Visible = True  ' Set this here when debugging but to make it go faster don't do it until the end
 
    Set myDestinationBook = appExcel.Workbooks.Open(FileName:="C:\Users\James\Documents\Trusted_Locations\CBF\Ministers_Churches_Database_4.xlsm", ReadOnly:=True)
 
    Set myDestinationSheet = myDestinationBook.Worksheets("Ministers")
 
    With myDestinationSheet
        .Range("A4:Q5000").ClearContents
 
        Set mySourceBook = appExcel.Workbooks.Open("C:\Users\James\Documents\Trusted_Locations\CBF\Last_CBF_DB_Export.xlsx")
 
        Set mySourceSheet = mySourceBook.Worksheets(1)
        Set Rng1 = mySourceSheet.UsedRange
        Set Rng2 = Rng1.Offset(1, 0).Resize(Rng1.Rows.Count - 1)
        Rng2.Copy
 
        myDestinationSheet.Range("A4").PasteSpecial (xlPasteValues)
 
        mySourceBook.Close SaveChanges:=False
 
        .Range("E1").Interior.ColorIndex = 6
        .Range("G1").Interior.ColorIndex = 6
        .Range("D4").Select
 
    End With
 
    appExcel.Visible = True
 
    ' this leaves the workbook and application open for the user but
    ' we can destroy our variable
    appExcel.UserControl = True
 
    Set appExcel = Nothing
 
ExitHere:
    Exit Sub

 

[jim may]

Bob, Thank you sooooooooooooo Much, Very helpful - Thanks for your time. Jim