Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
export excel data to access table using ADO
Excel sheet name - Sheet 1 and my data has header,
Access db name - is stored in cell B2 Access table name - Main Code : Sub Button14_Click() ' Exports data from the active worksheet to a table in an Access database Dim cn As ADODB.Connection Dim r As Long Dim LastRow As Long Dim dbfile As String dbfile = Cells(2, 2).Value Set cn = New ADODB.Connection strCon = "Provider=Microsoft.ACE.OLEDB.12.0; " & _ "Data Source= " & dbfile ' Late binding, so no reference is needed Set cn = CreateObject("ADODB.Connection") cn.Open strCon ' Find LastRow in Col A into the Sheet1 LastRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row ' Insert unto a table called Main scn = "[Excel 8.0;HDR=YES;DATABASE=" & ActiveWorkbook.FullName & "]" strSQL = "INSERT INTO MAIN " _ & "SELECT * FROM " & scn & ".[Sheet1$A3:AI" & LastRow & "]" ' Execute the statement cn.Execute strSQL cn.Close Set cn = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Data from Excel into existing Table in Access | Excel Programming | |||
excel worksheet export into access table | Excel Worksheet Functions | |||
Export Access Table to Excel | Excel Programming | |||
vb code to export data into existing Access table | Excel Programming | |||
Export Data to Access Table | Excel Programming |