0

I have a number of Excel-Files that contain data which I need to combine into one table.

Is it possible and reasonable to do this from within Access VBA code, which I would prefer? Or should I just do it in Excel VBA?

My idea was to iterate through the Excel-Files and use an SQL statement for UPDATE Column WHERE MyCondition, to get the Values I want to the Column I want. Is it possible this way or what would be a suggestion to solving this?

I have tried it this way until now from Access:

Sub Execute_UpdateQuery()
   Dim conn As ADODB.Connection
   Dim NumOfRec As Integer
   Dim strPath As String

   strPath = "D:\Sortierliste August_September\Test.xlsx"

   Set conn = New ADODB.Connection

   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath & ";Extended Properties=Excel 12.0 Xml;HDR=YES;"

   conn.Execute "UPDATE Ware SET Name = 'test'" & _
      " WHERE Name = 'Ackermann'", NumOfRec, adExecuteNoRecords

   Debug.Print NumOfRec & " records were updated."
   conn.Close
   Set conn = Nothing
End Sub

But that results into an error "Cannot find access-database module "Ware". I guess it is because it is just an excel-table and not a database? I thought it can find an excel-file containing the table called Ware and execute an sql query. Any help or suggestions would be great on how to solve this issue.

Greetz

Improve this question
2
Related questions
1
Updating a table Access and Excel VBA
0
can't call update query from Excel VBA
0
I'm trying to UPDATE my table in Access VBA
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.