Hello,

I need to transfer a section of data in excel to a table in access. The excel file is current open. I want to click a button then the data in a pre-defined range will be insert into a known table in access.

The code I have is listed below. I got an error message saying "Counld not use 'xxxx'; file already in use." xxxx is this excel file's name. DataToExport is the range I defined in excel.

Please help me solve this issue! Thank you very much!!!!

---------------------------------------------------------------
Set adoCn = New ADODB.Connection
adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"

sConnect = "Data Source=c:\test.accdb;Persist Security Info=False;"
sCommand = "Insert into table1 select * from DataToExport in '" & ThisWorkbook.FullName & "'"

adoCn.Open sConnect
adoCn.Execute sCommand

adoCn.Close
Set adoCn = Nothing