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