CRUD two file excel

[dave_yd]

Dear Chandooers

Does anyone has example interact between two files excel using ADODB to Create, Read, Update and Delete datas

Regards

 

[Nebu]

Hi:

There are lot of examples of this available in this forum just use the search box. Alternatively, you can get ready made code through Google search as well.

Note: You may need to tweak the codes a bit to suit your requirements.

Thanks

 

[dave_yd]

I am already try to find out both google and in this room, but I cannot find two excel files which interact update and delete using ADO. They are only 'Read'.
Please help me to show the link.

 

[Nebu]

Hi:

Here are some links to get you started.


http://www.ozgrid.com/forum/showthread.php?t=61901
http://www.w3schools.com/asp/ado_delete.asp
http://www.xtremevbtalk.com/tutors-corner/217783-using-ado-excel-files.html

Thanks

 

[dave_yd]

I am already tried this code in workbook 'EFPM Hauling 2016', but doesnot work. Please give me sample working code in my workbook for every tool 'export, import, delete and update.'

Sub ADOFromExcelToExcel()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long

  Set cn = New ADODB.Connection

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _

  "D:\EFPM\Database EFPM\Destinatefld\Logbook Hauling.xlsx;"



  Set rs = New ADODB.Recordset

  rs.Open "[DailyLog$]", cn, adOpenKeyset, adLockOptimistic, adCmdTable



  r = 2 ' the start row in the worksheet

  Do While Len(Range("A" & r).Formula) > 0



  With rs

  .AddNew ' create a new record

  ' add values to each field in the record

  .Fields("No") = Range("A" & r).Value

  .Fields("Date") = Range("B" & r).Value

  .Fields("Site") = Range("C" & r).Value

  ' add more fields if necessary...

  .Update ' stores the new record

  End With

  r = r + 1 ' next row

  Loop

  rs.Close

  Set rs = Nothing

  cn.Close

  Set cn = Nothing

End Sub

 

[Nebu]

Hi:

What is the purpose of this macro? As per my understanding you want to manipulate the "DailyLog" tab on the workbook "Logbook Hauling" using the form designed in "Input Hauling" tab on the workbook "EFPM Hauling 2016". If that is the case why don't you design a user form and do the manipulations from there. This is the most common and easiest method used for the data set up like yours.

Correct me If I am wrong.
Thanks

 

[dave_yd]

Nebu,

Thanks for your quick response.I am already prepare this workbook complete with userform and database at previous time. But they are not working properly in sharepoint. Now I want to separate Dailylog as Database, and sheet input in another file as userform.
I did it because, userform is not working in sharepoint. With created files which separated in two files, I will put Database in sharepoint. The file will input by some users from different places, and will save in database in sharepoint. While then another Superior will access data as they can do. Why Excel? Because they (users) are familiar in excel.

Thanks

 

[dave_yd]

Just for info,

Read Datas are working good in two excel using ADO, just click find tools at right side of 'Date' Cells F4. The tools are set its connection to Logbook Hauling and its work. But while I am trying create code to input (Update, Add New and Delete), it does not work. Unfortunately this Find Tools does not work in sharepoint, so I have to separate Input files and Database. Input Files will give to each users, and Database will put in share point.

Regards

 

[Nebu]

Hi:

I have coded for inserting(Importing) data from the "EFPM Hauling 2016" to "Logbook Hauling". I had to create a helper heading for the SQL to work properly. I have done it for Date and Site fields you will have to code it for rest of your fields. The syntax is really tricky you have to be absolute sure about your data types and query accordingly. You will have to follow similar approach for Delete and Update as well.

Public Sub WorksheetInsert()
  Dim Connection As ADODB.Connection
  Dim ConnectionString As String
  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.Path & "\Logbook Hauling.xls;" & _
    "Extended Properties=Excel 8.0;"
   
  Dim SQL As String
   
  SQL = "INSERT INTO [DailyLog$](B,C) VALUES(" & "#" & Sheet1.Range("C4") & "#" & ",'" & Sheet1.Range("C6") & "')"

  Set Connection = New ADODB.Connection
  Call Connection.Open(ConnectionString)
  Call Connection.Execute(SQL, , CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)
  Connection.Close
  Set Connection = Nothing
End Sub

The insert is the query , the length of this query will be depended on the nof fields you want to capture. Replace INSERT INTO with DELETE/UPDATE for deletion/updation, you may have include where statement to determine which record has to be deleted or updated.

Note: Save both the files in the same location.

Thanks