Discouraged with Access...

[jim may]

If I step thru the entire code (thru the last line shown - even though there is more) and I in the immediate window type in:

appExcel.Workbooks.Open("C:\Users\James\Documents\Trusted_Locations\CBF\LastExport.xlsx")

My Excel File does not have the proper data in it!!!
If I run the query "qryDataExportedToExcel" the correct data is there...
I;m getting VERY frustrated,,, Can someone assist?

TIA,

Jim

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
    Dim LR As Long

    On Error Resume Next

    Set appExcel = GetObject(, "Excel.Application")
    
    If Err.Number = 429 Then
    Set appExcel = CreateObject("Excel.Application")
    End If
    
    On Error GoTo 0
    'appExcel.Visible = True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryDataExportedToExcel", _
                              "C:\Users\James\Documents\Trusted_Locations\CBF\LastExport.xlsx", True
..........................

 

[SydneyGeek]

Jim, I took your code and modified only the query and the path:

Sub TestExport()
    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
    Dim LR As Long

    On Error Resume Next

    Set appExcel = GetObject(, "Excel.Application")
    
    If Err.Number = 429 Then
    Set appExcel = CreateObject("Excel.Application")
    End If
    
    On Error GoTo 0
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryBillingSummary", _
                              "C:\Users\Denis\Desktop\LastExport.xlsx", True
    appExcel.Visible = True
    Set appExcel = Nothing
End Sub

Naturally I added the cleanup for the Excel reference too. It works on my PC; if I remove some of the data from the Excel file and re-run the code, it repopulates the file as expected.
Is there something happening downstream in your code that prevents the update?
Do you already have the workbook open? If so that may prevent the code from locking the file correctly; Excel and Access clash under those circumstances. Try closing it, and re-run the code.

Denis

 

[jim may]

Thanks Denis,

Prior to running the code I would change a fields value (City: from Roanoke to Salem) in my Access Form
Stepping thru the code as I processed the DoCmd line I had my Explorer window open and visible to see my file LastExport.xlsx DateModified flash to reflect the update. I opened the LastExport.xlsx manually to see that although the update took place according to the DateModified flash the txet "Roanoke" still appeared in Cell D2, not the text "Salem" in cell D2; I even ran the Query and "Salem" appeared as expected...

I decided to completely clear all cells in my LastExport.xlsx (Rows 2 downward to end) and Save the File, and Close.

I ran the code AGAIN, and my Cell D2 showed "Salem".. I'm just not sure what's going on. I can only WISH/HOPE that subsequent running of the code will produce the most Up-to-Date info in my Access Fields. Below in the entire procedure - Note that I added
a few lines (IN RED) to clear the cells (Rows 2 and down) in the LastExport.xlsx file before Saving the file. Hopefully this will make it bullet-proof!!

Thanks again for your input, very helpful. Jim

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
    Dim LR As Long

    On Error Resume Next

    Set appExcel = GetObject(, "Excel.Application")
    
    If Err.Number = 429 Then
    Set appExcel = CreateObject("Excel.Application")
    End If
    
    On Error GoTo 0
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryDataExportedToExcel", _
                              "C:\Users\James\Documents\Trusted_Locations\CBF\LastExport.xlsx", True
    
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryDataExportedToExcel", _
                              "C:\Users\James\Documents\Trusted_Locations\CBF\LastExport.xlsx"

    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
appExcel.EnableEvents = False
        .Range("A4:R5000").ClearContents
    
        Set mySourceBook = appExcel.Workbooks.Open("C:\Users\James\Documents\Trusted_Locations\CBF\LastExport.xlsx")
appExcel.EnableEvents = True
        Set mySourceSheet = mySourceBook.Worksheets(1)
        Set Rng1 = mySourceSheet.UsedRange
        Set Rng2 = Rng1.Offset(1, 0).Resize(Rng1.Rows.Count - 1)
        Rng2.Copy
appExcel.EnableEvents = False
        myDestinationSheet.Range("A4").PasteSpecial (xlPasteValues)
appExcel.EnableEvents = True
appExcel.DisplayAlerts = False
'********************
Rng2.ClearContents
        mySourceBook.Close SaveChanges:=True

        'mySourceBook.Close SaveChanges:=False
        
'********************
appExcel.DisplayAlerts = True

        .Range("F1").Interior.ColorIndex = 6
        .Range("H1").Interior.ColorIndex = 6
        .Range("E4").Select

End With
    appExcel.Visible = True
With myDestinationSheet
    appExcel.ScreenUpdating = False

        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .AutoFilterMode = False
            With .Range("A3:R" & LR)
                .AutoFilter
                
            
            End With
            
    .Columns("E:E").EntireColumn.NumberFormat = "mm/dd/yyyy"
    .Columns("H:H").EntireColumn.AutoFit
End With
    
    ' this leaves the workbook and application open for the user but
    ' we can destroy our variable
    'appExcel.UserControl = True
    appExcel.ScreenUpdating = True
    Set appExcel = Nothing

End Sub

 

[xenou]

It's possible the change has not been committed. Some changes/updates are visible to the the user but would be invisible to other users. To test the theory, make the change, then close the form. Then run the code (even if you have to open the same form again - to check for the change you made before closing it).

Note: For what it's worth (good or bad I don't know) but I usually Kill (Delete) the existing file before exporting a new one. This is probably because I'm too lazy to bother remembering if Access overwrites or appends when the file already exists.