Appending an Excel Spreadsheet with Access Table 1

[irethedo]

Hi there-

I have an Access table that I am using to append data to an Excel spreadsheet but am seeing something peculiar that I am hoping that
someone can explain so that I can fix this issue...

I am using the following code to export my table (Sales_Excel_tbl) into an Excel Spreadsheet (Sales.xlsx):

    Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Sales_Excel_tbl", dbOpenSnapshot)
    
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open("C:\My Stuff\Sales.xlsx")
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets("NewSales")         'RSP
        objWkb.Worksheets("RSP").Activate
'        objWkb.Windows("RSP").Visible = True

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
    End With
    
    objWkb.Save
    objWkb.Close
    objXL.Quit
    
    With objXL
        .Visible = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set objXL = Nothing
    Set rs1 = Nothing

My table (Sales_Excel_tbl) looks like this:

[pre]Sales_Excel_tbl:
Date Cust_Name Sales # System Note
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8 - 615968 - 1619 - ENC - 61244 - 6665 - 61244 - 61574 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8 - 61244 - ENC - 61244
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8 - 61244 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8 -61244
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-66-8
5/26/2016 VALLEY CENTER 1419266665 SSA-566-8 - ENC - 61244 - 1619 - 61244 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 615968 - 1619 - ENC - 61244
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - ENC - 66716 - 61244 - 1619 - 165488
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - ENC - 61244 - 615968 - 1619 - 61244 - 615968 - ENC
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 1619 - ENC - 61244
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 1619 - ENC - 61244
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8 - 61244 - 1619 - ENC - 61244
[/pre]

But the peculiarity that I am seeing is that everything in the table (Sales_Excel_tbl)is being exported to the spreadsheet (Sales.xlsx)
except for the Notes Field.

After the export has been completed, the following data has been exported to the NewSales worksheet of the spreadsheet (Sales.xlsx) from
the table (Sales_Excel_tbl):



[pre]Date Cust_Name Sales # System Note
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-162-8
5/26/2016 VALLEY CENTER 1419266665 SSA-66-8
5/26/2016 VALLEY CENTER 1419266665 SSA-566-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8
5/26/2016 VALLEY CENTER 1419266665 SSA-161-8[/pre]

Any ideas what I am doing wrong or why the Notes field is not being exported?

Thanks

 

[irethedo]

So I put a break point on the [highlight #FCE94F]"With objXL"[/highlight] line and set up a watch variable for
rs1!Note and the watch variable shows this field to be blank (not a " null", even though the table shows data in this field...

Not sure why this is happening when the table shows data in that field...

Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Sales_Excel_tbl", dbOpenSnapshot)
    
   [highlight #EDD400] With objXL[/highlight]
        .Visible = True

 

[Andrzejek]

How is this [tt]Note[/tt] field defined in your Access data base?
Is that a Memo field? Or a Text field?


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Thanks Andy-

I wasn't even looking at that but I have a series of queries that set up different tables and this field was being set up as
a Binary type. After seeing your post, I noticed this and I switched it over to to a textfield and this fixed the problem.

Thanks again

 

[irethedo]

The funny thing is that now all of the data is being exported but it is all in underlined text...