INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Appending an Excel Spreadsheet with Access Table

Appending an Excel Spreadsheet with Access Table

(OP)
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):


CODE -->

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:


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
 

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):




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 

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

Thanks

RE: Appending an Excel Spreadsheet with Access Table

(OP)
So I put a break point on the "With objXL" 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...

CODE -->

Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Sales_Excel_tbl", dbOpenSnapshot)
    
    With objXL
        .Visible = True 

RE: Appending an Excel Spreadsheet with Access Table

How is this Note 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.

RE: Appending an Excel Spreadsheet with Access Table

(OP)
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

RE: Appending an Excel Spreadsheet with Access Table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close