Copy multiple ranges with values only in some

[Lol999]

Hi, this one has had me running round in circles for a bit tonight:banghead:

I'm copying multiple ranges of cells from excel into access and it works fine EXCEPT two of the columns, k and l are calculated. K is a summation of 5 columns i.e a1+b1+c1 etc and L is a date calculation, a specific cell displays a date and each cell in column L adds 7 to it (this is necessary for when exported to Access).
I need to copy the values only from columns K and L, as it stands I'm getting only the first cell in each row for column K and a date set in 1900 for column L.

I've tried lots of options but getting nowhere so please help if you can.

Here's the code:

Sub Button1_Click()
Const acCmdPasteAppend = 38
Sheets("June 12th").Select
Range("a5:a30, b5:b30, c5:c30, k5:k30, l5:l30").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

Thanks, Lol

 

[isladogs]

Create new columns M & N in Excel which are the VALUES of these cells in columns K & L. Then import columns M & N instead of K & L

BTW Access stores all dates as long integers starting around 1900 (30/12/1899?)
As column L contains no data Access treats it as day 0

 

[Lol999]

Hi Ridders, thanks for the reply. I had considered that but wondered if there was a code based solution.

Many thanks, Lol

 

[isladogs]

I may be wrong but AFAIK - no.
As far as Access is concerned the columns K & L are empty

You could test this easily enough by changing a couple of rows to the values in Excel.
Then go back to Access & see if it works for those rows

 

[Lol999]

Interestingly, or rather frustratingly I cannot get only the value into the new columns. using the "=VALUE" function is as bad as before.
Annoying.

I've just tried putting values only into the columns and it still doesn't work properly so it's not a formula issue.

 

[arnelgp]

maybe you can use ADODB to insert records to your table.

Private Sub Button1_Click()
Dim intStartRow As Integer
Dim intEndRow As Integer
Dim i As Integer
Dim adoCon
Dim adoRs
Dim strSQL As String

Dim strDBName As String
Dim strMyPath As String
Dim strDB As String

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

Set adoCon = CreateObject("ADODB.Connection")
adoCon.Open "Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = " & strDB & ";" & _
"Persist Security Info = False;"

strSQL = "SELECT * FROM Tbl_Trial;"

Set adoRs = CreateObject("ADODB.Recordset")

'Set the cursor type we are using so we can navigate through the recordset
adoRs.CursorType = 2

'Set the lock type so that the record is locked by ADO when it is updated
adoRs.LockType = 3

'Open the tblComments table using the SQL query held in the strSQL varaiable
adoRs.Open strSQL, adoCon


'This is your starting row and ending row from
'the code you posted
intStartRow = 5
intEndRow = 30

For i = intStartRow To intEndRow
'!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
' WARNING
' be sure not to include AutoNumber field in your update
'
' In my example I exclude adoRS(0), I was thinking
' maybe this is AutoNumber field, so I started
' with adoRS(1), the second field.
' adjust as necessary!

'Tell the recordset we are adding a new record to it
adoRs.AddNew
adoRs(1).Value = Cells(i, 1) 'Column 1=A
adoRs(2).Value = Cells(i, 2) 'Column 2=B
adoRs(3).Value = Cells(i, 3) 'Column 3=C
adoRs(4).Value = Cells(i, 11) 'Column 11=K
adoRs(4).Value = Cells(i, 12) 'Column 12=L
adoRs.Update
Next
adoRs.Close
adoCon.Close
Set adoRs = Nothing
Set adoCon = Nothing

End Sub

 

[Cronk]

Funny. Code works fine for me (at this site using Office 2007)

 

[Lol999]

Funny. Code works fine for me (at this site using Office 2007)

Must be a version issue, I'm running 2013 and it just won't work.

Nevermind, arnelgp has provided what appears to be an elegant solution.

 

[isladogs]

After reading Cronk's reply, I'm just going to eat my own words....

What the OP wrote in post #1 seemed logical to me.
I explained what I thought was the reason without testing it.
Normally I do it the other way - import from Excel into Access

I've now tested exporting from Excel 2016 and the code in post #1 also worked for me with one caveat.

Range("a5:a30, b5:b30, c5:c30, k5:k30, l5:l30").Select

The specified range is ignored - all columns with data are exported to Access
I've tried it where the destination table has the fields predefined & where only the PK ID field is defined - BOTH work (exactly as in Cronk's screenshot)

However, further testing where the ID field is created but not defined as a PK, 26 records are created for rows 5 - 30 but none of the other columns are created/populated.

I then compared the benefits of each method of importing the data into Access from Excel
To save time I just used the wizard but would use code if it was to be done regularly

As I already knew, each method works but which is better?
a) Import to a new table - this allowed me to select the columns I wanted
b) append to an existing table - this also works providing the Excel worksheet had the same headers in row 1 as the destination table
All columns must be imported
c) link to the Excel worksheet then use an Access query to import just the required columns to the table

In conclusion:
1. exporting from Excel to Access works with both data & formulas but gives you less control about what is exported
2. importing Excel data into Access also works with both data & formulas

Of the 3 methods, the least satisfactory is appending to an existing table.
The other 2 methods give you FULL control over what is imported