Updating data in a closed workbook.

[V.B.A.02]

Hello, I am looking for some help to paste or update (append) data in an Excel table (in closed workbook) from another Excel table (in open workbook).

both tables have same fields and data types.

I am sure this can be done using ADOB connection and SQL queries.

Any help is appreciated.

Thanks.

[YasserKhalil]

Hello
Have a look at this sample files
In the workbook named "Import Data From Closed Workbook Using ADO" click first the button "Get File Path" then select your closed file ..
then click the button "Get Data From File" ...

[V.B.A.02]

Hello Yasser.!

It works well when data has to be copied from a closed workbook in an open workbook.
However I want data to be copied (append) to a closed workbook from an open workbook. Any help?

Regards

[YasserKhalil]

I don't know if it possible or not but see that link (another approach)
http://www.ozgrid.com/forum/showthread.php?t=69735

[YasserKhalil]

After I have searched I found little resources for that but could figure it out from many resources
Have a look at this sample files (may be good start point)

[V.B.A.02]

Thanks Yaseer, that would work.

[V.B.A.02]

Can you help me with this following code:

Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msado15.dll"

it gives me following error
Run-time error '1004':
Method 'VBE' of object' _Application' failed.

any help?

[V.B.A.02]

Thanks, It works now, I forgot to change macro security..

[YasserKhalil]

You're welcome. Glad I can offer some help for you

[V.B.A.02]

Hello Yasser,

Could you please help me with this code:

This code pastes data form an open workbook to a closed workbook successfully for only two columns (there could be many rows), But I need to add data in closed worbook for many coulmns not limited to 2 columns.

Please suggest.! Thanks

Sub ExportToClosedWorkbookUsingADO()
    Dim conn  As New ADODB.Connection, rs As New ADODB.Recordset, strFile As String
    Dim i As Long, j As Long
    strFile = ThisWorkbook.Path & "\Sample.xlsm"
    #If VBA7 And Win64 Then
        Set conn = CreateObject("Adodb.Connection")
        conn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & strFile & ";" & "Extended Properties=""Excel 12.0;HDR=Yes"""
    #Else
        Set conn = CreateObject("ADODB.Connection")
        conn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strFile & ";HDR=Yes';"
    #End If


    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [Master File$]", conn, 1, 3
    
For j = 2 To Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    rs.AddNew
    For i = 0 To Sheets("Data").Cells(1, Columns.Count).End(xlToLeft).Column - 1
        rs(i).Value = Sheets("Data").Cells(j, i + 1).Value
    Next i
    rs.Update
Next j
    
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "Updated...", vbInformation
End Sub

[mdmackillop]

Never worked with this before but looking at YK's post 5.
This will return the (I think) the UsedRange from Sheet1; Add headers to Row 1 in Sample to the number of columns you require.
I've not tested if UsedRange excludes leftmost columns.

    rs.Open "SELECT * FROM [Sheet1$]", conn, 1, 3

This should populate those columns

rs.AddNew
    For i = 0 To rs.Fields.Count - 1
        rs(i).Value = Sheets("Sheet1").Cells(2, i + 1).Value
    Next i

[V.B.A.02]

I have found solution for this on web, Solution says : Field Name should not be any reserved keywords used in VBA like 'Name' or 'Date'.

I tried this by changing field's name, however I am sure it that is the solution because it is working with one of my codes and not working with other.

I am hoping Yasser to comment on this. He may explain this better or may be any one else can.

[mdmackillop]

Sample attached