vba help

[vishal120]

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

[vb5prgrmr]

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

[AndreRet]

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 
Declarations
Dim con As New ADODB.Connection
Dim con1 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset

Code

'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()
rs1.Delete
rs1.MoveNext
Loop

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

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

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

Private Sub Command2_Click()
End
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"), "")
            
    Else
        datagrid1.TextMatrix(i, j) = IIf(IsNull(rs.Fields(j).Value), "", rs.Fields(j).Value)
    End If
    
Next
i = i + 1
If datagrid1.Rows <= i Then
    datagrid1.Rows = datagrid1.Rows + 1
End If
rs.MoveNext
Loop
datagrid1.Rows = datagrid1.Rows - 1
datagrid1.AllowUserResizing = flexResizeBoth
End Sub

[vishal120]

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

[AndreRet]

As far as I know, a locked cell should not stop you from copying the data. I might be wrong though. See my attachment with many options on thread http://www.daniweb.com/forums/thread223918.html.

Hope this helps.