Update Excel data from access table through SQL in VBA

[anandagrawal444]

Hello,

I am getting the error "You cannot edit this field as it resides in a linked excel spreadsheet".

I am trying to create a macro in excel to update data in excel sheet from access table. The code used in VBA is as follows :

Sub barcode_retrieve()


Set cn = CreateObject("ADODB.Connection")
DBPath = "C:\Users\HP\Documents\Access to Excel" & "\Payment_Management2.accdb"
dbWb = "C:\Users\HP\Documents\Access to Excel\Payment Management.xlsm" ' file name to be changed
dbWs = "Data Entry"
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath
dsh = "[" & "Data Entry" & "$]"
cn.Open scn

Dim oRs As ADODB.Recordset

sSQL = "INSERT INTO [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
sSQL = sSQL & "SELECT a.[Barcode_No],a.[Barcode_Category] FROM Barcode a where a.Receiving_No is null"

cn.Execute sSQL

End Sub

Please suggest way forward to update excel data from access database.

[ranman256]

create a query to pull data out of the database and put in excel using COPYFROMRECORDSET

Set oRs = cn.Execute("qsQuery")
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Co pyFromRecordset rs

[anandagrawal444]

Thanks for the reply. This method will display the results in continuos columns starting from column A1. But I have to display the data in non adjacent columns. Example - Barcode No in Column A in excel, Barcode category in Column C in excel, like that. Through SQL I can match the column names in excel and Access and display the data in the required columns in excel.