Linking Excel Cell Data With Access

[webking]

I have a sheet in excel on which i do entries on daily basis and a new sheet comes in act each day.
Now i want to store the daily report data frome excel to access for making weekly & monthly reports.
Please guide me how i can do this. Like if i want that from cell A1,A2,A3 data on daily basis goes to access and store under the heading of Expenses how i can do that.

[Ziggy]

Import your spreadsheet to Access to create a new table. Set your primary key as date or as a autonumber ID.

now each day just run a import macro (from access) The data should continue to add to the table with out overwriting.

Ziggy

[Yogi Anand]

Hi webking and Ziggy:
Would there be any merit in linking the ACCESS Table to a Worksheet or a certain range in the Worksheet -- just a thought!
This might make sense because it appears you still want to do your processing in EXCEL and you want to use ACCESS as a front for reporting.

[Ziggy]

I thought about that also, but I figured collecting the data in access via the import would allow him to simply overwrite the Excel file each day as it sounds like this is a daily process.

If the table is linked then there has to be a way to append the data so it is not lost when the file is overwriten. Possibly a Append Query?

If archiving the data is not required, then I think the link would be the best option.

Ziggy

[Tommy Bak]

Hi
I hope this example can help you getting startet.Sub AddRecordToAccess()
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("C:Test.mdb")
Set rs = db.OpenRecordset(Name:="Test", Type:=dbOpenDynaset)'Tablename
With rs
.AddNew
.Fields("Name").Value = Range("A1")
.Fields("Age").Value = Range("A2")
.Fields("Phone").value=Range("A3")
.Update
End With
rs.Close
db.Close
Set rs = Nothing
End Sub

regards Tommy

[webking]

Thankyou all .

Please explain this code like how to use it according to my needs .

[justa]

You can actually link to an Excel file from Access. It depends how you are using the Excel entry sheet. If you continue to add daily activity on the next available row so that one sheet has ALL data, then this may work for you.

1. The Excel file must have column headings starting on row 1, and all cell formats must be the same (i.e. don't enter numbers and characters in the same field)

2. From Access select the Tables Tab, New, Link Table, and change the file type to Excel.

3. Now you can create reports using the linked sheet as your data source.


Did I read your message correctly, that you are using data from ACT? If so, you may be able to save as a text file and import directly to Access.

[Tommy Bak]

Hey
Open your worksheet, press Alt-F11.
In the VBA editor insert a new module and copy this code into that.
Put in your actual database name where it say C:testbasebase.
Put in your actual table-name where it says Testtable.
In the menu "Tools / references" checkmark "Microsoft DAO 3,6 object

library".
Alt-F11 to get back to the worksheet.

Now you're ready to run the macro from "Tools/macro"
You can also assign a button to this macro.
Be aware that the macro does not check the database to see if todays

record is allready put in. You can, by mistake, use the macro more than

once per day.


Sub AddRecordToAccess()
Dim db As Database
Dim rs As Recordset

'opens a link to the msaccess database named testdatabase
Set db = OpenDatabase("C:Testdatabase.mdb")

'opens a link to the table named testtable in the database Testdatabase
Set rs = db.OpenRecordset(Name:="Testtable", Type:=dbOpenDynaset)

'Put A1, A2 and A3 into 3 new databaserecords in fieldname "Expenses"
With rs
for x = 1 to 3
.AddNew
.Fields("Expenses").Value = Range("A" & x).value
.Update
Next
End With

' close table
rs.Close

'close database
db.Close

'Clean up
Set rs = Nothing
End Sub



regards Tommy

[webking]

Tommy, thanks a lot for your help. i really appricetae waht u have done for me.
Kindly guide me how i can set excel to search a praticular thing.

can i email you.
talentshow@yahoo.com

[Tommy Bak]

Yes.
Tommybak@netscape.net
Regards Tommy