CODEWITHSUNDEEP
ms-accessexcelvba
KillerSnail
KillerSnail

Reputation: 3591

Exporting ListObject from Excel to Access

I am writing a macro that will run from an excel workbook and export out one of the ListObjects to my access file.

I have this VBA code at the moment:

Sub AccessImport()

' Create connection
Dim Path As String
Dim conn As Object
Dim connectstr As String
Dim recordset As Object
Dim strSQL As String

Path = "P:\CALIBRE-YSP Implementation\11 General\CDM Database"

Set conn = CreateObject("ADODB.Connection")

connectstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & Path & "\CDM_Database_DataOnly.mdb;"

strSQL = "SELECT * INTO DeliverablesLivesheet FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"


conn.Open connectstr
Set recordset = conn.Execute(strSQL)

recordset.Close
Set recordset = Nothing

conn.Close
Set conn = Nothing

End Sub

It is supposed to take a ListObject from Excel then transfer the data to a new access table, overwriting the old one.

It throws an error when it tries to execute the SQL:

strSQL = "SELECT * INTO DeliverablesLivesheet FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"

So I guess something is wrong with the SQL?

I can't seem to work it out though

Thanks

Upvotes: 1

Views: 1114

Answers (2)

user2230817
user2230817

Reputation: 

strSQL="insert into DeliverablesLivesheet Select * FROM [Excel 8.0;HDR=YES;DATABASE=P:\CALIBRE-YSP Implementation\11 General\CDM Database\CDMv2 - Development.xlsm].[DeliverablesImport];"

Upvotes: 1

KillerSnail
KillerSnail

Reputation: 3591

Ok I didn't realise that the connection string works like:

[Excel 8.0;HDR=YES;DATABASE={address of file}].[{sheet name}]

I had the name of the ListObject in there.

Now I get stuck with "Database or object is read only.

Upvotes: 0

Related Questions