Hi all,

I need help please!

I have this code to append/insert an excel file into an access table but before I make some modifications in my excel file (delete, sort ,etc)

After the modifications I run the below code to insert the data into the access

but when I run this line -> con.Execute strSQL ,
the original file is opened and the command copy the data before the modification

VBA CODE:

DBPath = "O:\xxxx\xxxx\db.accdb"
app - file with the modifications

Public Function exportDatafromExcel(ByVal DBPath As String, ByVal app As Excel.Application)

Dim con As ADODB.Connection

Dim strCon As String
Dim lasrow As Integer
Dim lascol As String
Dim a, strSQL As String

Dim book As Excel.Workbook
Dim sheet As Excel.Worksheet

Set bookapp = app.ActiveWorkbook
Set sheetapp = bookapp.Worksheets(1)

lastrow = ExcelFunctions.excelLastUsedRow(sheetapp)
lastcol = ExcelFunctions.ColumnLetter(ExcelFunctions.excelLastUsedColumn(sheetapp))

'=============================================================
' Conecto to Access DataBase
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & DBPath & ";"

Set con = New ADODB.Connection
con.Open strCon
'=============================================================

'Append into a Table
'HDR= Yes-- First Line have the header
a = "[Excel 8.0;HDR=YES;DATABASE=" & "O:\xxxxx\xxxx\testeT4.xls" & "]"

strSQL = "INSERT INTO DataTable1 " _
& "SELECT * FROM " & a & ".[" & sheetapp.Name & "$A1:W" & lastrow & "]"

con.Execute strSQL
con.Close
Set con = Nothing
End Function