Hi friends am actually working on a project in my work. i have one excel file which i have shared and now is become bigger in size as i have formulated the cells and also with the amount of data it has.
i want to use vba to place the formula so that the files do not become bigger in size.
every day i have to copy the data which is completed to another sheet for further process.
i want to use vba to do this but till now am still struggling hard.
is there a way to make these two files autosyncronized at certain time interval.
many thanks in advance for the help
here is my mail address: SNIP

Edited 7 Years Ago by happygeek: keep it onsite

No, you would have to open the one up that contains the sync macro and the easiest way would be to record a macro that does what you want.

Good Luck

I'm not sure if this will help in VBA, but definately in vb6. I've written this quite some time back, hope it is error free.

'Task: Getting the excel file in to the grid and store that data in Microsoft Access DB 
Dim con As New ADODB.Connection
Dim con1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset


'here iam using the Msflex grid with the name datagrid1

Private Sub Command1_Click()
con1.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\karthik\mydb.mdb;Uid=Admin;Pwd=;")
Set rs1 = New ADODB.Recordset
rs1.Open Source:="grid", ActiveConnection:=con1, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
'grid is the tablename in access
Do While Not rs1.EOF()

datagrid1.Cols = rs.Fields.Count
Dim increase, row As Integer
increase = rs.Fields.Count - 1
j = 1
Do While Not rs.EOF
For i = 0 To increase
rs1(i) = datagrid1.TextMatrix(j, i)

rs1(i) = Format(Now, "dd/mm/yyyy")
j = j + 1

Set rs1 = Nothing
Set con1 = Nothing
datagrid1.Rows = 0
End Sub

Private Sub Command2_Click()
End Sub

Private Sub html_Click()

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\karthik\testfiles\ipo290406.xls;Extended Properties=""Excel 8.0;HDR=no;IMEX=1""")
Set rs = con.Execute("select * from [Sheet1$]")

i = 1
datagrid1.Rows = 2
datagrid1.Cols = rs.Fields.Count
Do While Not rs.EOF
For j = 0 To rs.Fields.Count - 1
    'datagrid1.TextMatrix(0, j) = IIf(IsNull(rs.Fields(j).Name), "", rs.Fields(j).Name)
    If j = rs.Fields.Count - 1 Then
        On Error Resume Next
            datagrid1.TextMatrix(i, j) = IIf(IsDate(CDate(rs.Fields(j).Value)), Format(CDate(rs.Fields(j).Value), "dd/mm/yyyy"), "")
        datagrid1.TextMatrix(i, j) = IIf(IsNull(rs.Fields(j).Value), "", rs.Fields(j).Value)
    End If
i = i + 1
If datagrid1.Rows <= i Then
    datagrid1.Rows = datagrid1.Rows + 1
End If
datagrid1.Rows = datagrid1.Rows - 1
datagrid1.AllowUserResizing = flexResizeBoth
End Sub

HI the problem is that the other sheet is partly protected and it cannot take the data from there and the problem is i cannot unlock it because other user can delete the formula. this is why i cannot record a macro directly.

No, you would have to open the one up that contains the sync macro and the easiest way would be to record a macro that does what you want.

Good Luck

This article has been dead for over six months. Start a new discussion instead.