Code hanging on acCmcPasteAppend

[Lol999]

I'm attempting to copy data from an excel sheet to a table in an access db. I came across some nice simple code, put in my own parameters, and just the ONCE it worked!
Now, despite everything appearing to be "as it was" it hangs with the db table open but nothing pasted into it and when debugging the vba in Excel it reports error 2501 runtime command cancelled.

Any ideas anyone?
code below:

Sub Button1_Click()
Sheets("June 12th").Select
Range("a3:p58").Select
Selection.Copy

strDBName = "Data Export Trial.accdb"
strMyPath = "c:\Users\Lol\Desktop"
strDB = strMyPath & "\" & strDBName

Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB
appAccess.Visible = True

appAccess.DoCmd.OpenTable "Tbl_Trial", acViewNormal, acEdit
appAccess.DoCmd.RunCommand acCmdPasteAppend

appAccess.CloseCurrentDatabase
appAccess.Quit acQuitSaveAll
End Sub

 

[static]

Add

Const acCmdPasteAppend = 38

At the top of the code

Sub Button1_Click()
   Const acCmdPasteAppend = 38
   Sheets("June 12th").Select

 

[Lol999]

Brilliant! What does it do?

Hang on, not so brilliant it's offsetting data in the table the rows are not pasting as they are copied from the worksheet?

 

[static]

acCmdPasteAppend is constant (named value) that Access understands but Excel doesn't.
If you had option explicit turned on, your code wouldn't have compiled because acCmdPasteAppend wasn't declared anywhere.

Excel generated a variable automatically for you but it had no value and Access didn't know what to do with it.

 

[Lol999]

Brilliant! What does it do?

Hang on, not so brilliant it's offsetting data in the table the rows are not pasting as they are copied from the worksheet?

In fact the whole things disjointed.

Any ideas?

 

[static]

If you mean that the data isn't shown in the same order that it is the spreadsheet after you refresh (close and reopen the table, whatever)...

You have an index on one or more of the fields in the table.

Either delete the index, or add an autonumber field and sort on that.

 

[Lol999]

What I mean is columns are being shuffled down past their corresponding rows, blank lines inserted where there were none. Curious.

 

[static]

Can you take a screenshot and upload it?

 

[Lol999]

I've uploaded a couple of screenshots

 

[static]

The range you are selecting starts at A3. I can't really tell, but it looks like there might be merged cells on that line. Merged cells probably wont copy properly.

Values will only be filled in if they exist. i.e.
Job Code for Tom W is blank because A22 is blank. The copy process wont autofill for you.

Have any Job Codes copied over?

Also, are you copying to a table that already holds data or an empty table?

 

[Lol999]

I'll upload the databse so you can see exactly what's happening.

 

[static]

If you could upload the spreadsheet too that would be great.

I'll look into it tomorrow if nobody else steps in.

 

[static]

Open the table in design view.
Click in the Job Code field.
In General Field Properties
Change Indexed from 'Yes (Duplicates OK)' to 'No'

Check your data

 

[Lol999]

works perfectly now, thank you. I didn't realise Access would be enforcing data rules in a stand alone table with no primary key allocated.

Thank you.