General discussion

Locked

Export Excel data to Access Database

By aryana79 ·
Hi-

I am looking to export data I have in an excel worksheet to an Access database using VBA. I need this to be automated. There are three worksheets in my workbook. They contain text boxes and perform calculations through VBA. The third worksheet takes the data from the text boxes and make a plain old spreadsheet out of it. What I want is when the user clicks on a save button, I want to send the data in that third spreadsheet to an Access Database. I am trying to figure out exactly how I should do this. Actually if anyone knows how to get the data directly from the text boxes on the spreadsheet that would be even better. Right now I just created the third sheet with the data cause I figured it would be easier to export the data like this. If anyone has any code or suggestions of where I can look, I would really appreciate it.

Thanks,

Aryana

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Export Excel data to Access Database

by brian In reply to Export Excel data to Acce ...

Access does a good job of pulling information in from Excel. Why not have it export to another Excel file and then have Access pull from that same file?

Collapse -

Export Excel data to Access Database

by aryana79 In reply to Export Excel data to Acce ...

Poster rated this answer

Collapse -

Export Excel data to Access Database

by PID In reply to Export Excel data to Acce ...

Hi Aryana,

Why not try to use ADO, I have used it in VB and it should work in VBA. You should be able to load your third sheet from access at start up and save it again when your save button is clicked.

I don't see why you can't save the data directly to the Access Database from you text boxes with ADO.

Cheers
Pierre

Collapse -

Export Excel data to Access Database

by aryana79 In reply to Export Excel data to Acce ...

Poster rated this answer

Collapse -

Export Excel data to Access Database

by satheesh In reply to Export Excel data to Acce ...

Hi Aryana,

In Access, you can open a file import a file.
what u have to do is, import from that Excel file, and save that as Access file.

Steps.
1. Import from Access
2. Excel file which what to be exported.
3. Save that Access file.

Collapse -

Export Excel data to Access Database

by aryana79 In reply to Export Excel data to Acce ...

Poster rated this answer

Collapse -

Export Excel data to Access Database

by Shanghai Sam In reply to Export Excel data to Acce ...

Some questions...It sounds like you want Excel to push the data, rather than have Access pull it, correct? If so, then the push code will execute (as you have said) with the user selecting a 'save' button in Excel. If so, then do you only want to send the saved record - or push all the data across again?Do you want the data transfer to occur without the user doing anything else?
Why not have Access import the 'third' spreadsheet into a table, when you need to do something with the data? Think of the size of data that would need exporting, how often the data changes, how often you need to query the data (with Access)?
Is programming an Access DB with a similar interface to Excel an option?
Otherwise, try this:

Sub Button1_Click()
Dimdbs As Database
Dim rst As Recordset
Set dbs = DBEngine.Workspaces(0).OpenDatabase("C:\DB\db1.mdb")
Set rst = dbs.OpenRecordset("tblImport", dbOpenDynaset)
With rst
.AddNew
!Field1 = 46
!Field2 = 90 .Update
End With
dbs.Close
End Sub

This works for a table called tblImport that resides in C:\DB\db1.mdb with fields called Field1, Field2, Field3 etc. NOTE: You will have to add 'Microsoft DAO 3.51 Object Library' to your project under Tools, References
Hope this all helps... good luck

Collapse -

Export Excel data to Access Database

by aryana79 In reply to Export Excel data to Acce ...

Thanks for your help. I actually figured out the same code and it worked for me. Actually if you could help me with something else, that would be great. What code would I use to then pull the data I pushed to the Access DB back to the excel spreadsheet when the user logs back in. Basically I have a little entry page set up where the user enters a well name. I want the user to be able to pull up their old data when they reenter the system, which is the only reason there is really a database at all. Just so the user doesn't have to keep reentering information. If you understand at all what I am saying and could help that would be great. Thanks again for your help, I really appreciate it.

Aryana

Collapse -

Export Excel data to Access Database

by aryana79 In reply to Export Excel data to Acce ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums