Spreadsheets and Access db
Spreadsheets and Access db
(OP)
Hello,
I am a new member of this forum, seeking for the best solutions on how to manage the data from the spreadsheet.
The example of the projects is take the data from two spreadsheets and process the data (sort it, compare it, calculate)
What i did till today is I imported the spreadsheet (using VBA) into Access tables, joined them and built quiries. But I find this way very inefficient. Data gets lost when imported, the data types are not matching.
What is the best way to work with multiple spreadsheets in terms of automating some processes for the user?
I will appreciate your comments.
I am a new member of this forum, seeking for the best solutions on how to manage the data from the spreadsheet.
The example of the projects is take the data from two spreadsheets and process the data (sort it, compare it, calculate)
What i did till today is I imported the spreadsheet (using VBA) into Access tables, joined them and built quiries. But I find this way very inefficient. Data gets lost when imported, the data types are not matching.
What is the best way to work with multiple spreadsheets in terms of automating some processes for the user?
I will appreciate your comments.





RE: Spreadsheets and Access db
RE: Spreadsheets and Access db
RE: Spreadsheets and Access db
I'm making a lot of assumptions here. If you have data in two separate Excel spreadsheets, you can do all the text and numeric manipulations you want. I'm not sure why you would need any SQL statements. Can you give us a little more insight on the data?
How does the data get in the spreadsheet? What kind of calculations/tasks do you want to automate? If you're using VBA you're probably already familiar with assigning macros to key combinations or menu buttons. If not, I can continue.
Patrick
RE: Spreadsheets and Access db
What I was going to do is to create a user interface, place
CommonDialog on the form so the user would be able to browse for the files.
Then I import the excel spreadsheets into Access tables and then query on them.
THe problem is that some data gets lost due to conversion failure and I can't control it.
I tought there is a more efficient way to manipulate the data from the spreadhsheets.
Thanks.
RE: Spreadsheets and Access db
RE: Spreadsheets and Access db
keyardee, using MS Query, you can import data from about anywhere. From the menu select Data>Get External Data>New Database Query. Select Excel Files as your data source. You'll probably figure it out from there. If you want to see what the code looks like, turn on the macro recorder and see what it gives you.
patrick