Solved: Excel data backup to MS Access

Printable View

12-09-2008, 11:44 AM
rahullambe

Solved: Excel data backup to MS Access

Hi dear friends..

I have prepared a break tracker in excel sheet. I want particular data that to be stored in MS access table when I click "save" button on the Excel sheet.

I have a data in C5, D5, E5, F5 & G5 cells.

Please help me.

Thanks.:help
12-09-2008, 11:56 AM
Bob Phillips

Why not just save a copy of the workbook?

Or save the data in another Excel workbook, appending to it.
12-09-2008, 12:29 PM
rahullambe

Quote:

Originally Posted by xld

Why not just save a copy of the workbook?

Or save the data in another Excel workbook, appending to it.

Dude its a timesheet tracking file, I want that kind of information if employee save the data then they could not edit this file again or they can not make any other changes into that file. and the important thing there are 10 people who have different worksheet I want all 10 people data on MS access when they click on the save button.
12-09-2008, 01:02 PM
Bob Phillips

I can't see that changes my suggestion. Either do it all in Excel, or all in Access is my opinion.
12-09-2008, 01:34 PM
rahullambe

Quote:

Originally Posted by xld

I can't see that changes my suggestion. Either do it all in Excel, or all in Access is my opinion.

Ok fine thanks.
12-09-2008, 01:40 PM
Kenneth Hobs

You can use something like this. Notice that the first row must contain the Field name. I would suggest that you make a Hidden sheet, add the first row column names and then type "=" in the formula bar and click the cell from another sheet for that column. You then have 2 rows.

[VBA]Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub[/VBA]

Replace:
[VBA] nwindPath = ThisWorkbook.Path & "\nwind.mdb"[/VBA]
with your path to your mdb.

Replace:
[VBA] Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"[/VBA]
with your range. e.g.
[VBA]WorkSheets("YourHiddenSheetName").Range("A1:B6").Name ="MyRange"[/VBA]
Or, if you have manullay named MyRange, you can skip this part.

After doing this in our button's Click Event, save and close the file for them with your special password.