Spreadsheets and Access db

[sabavno]

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.

 

[plawrence]

Why don't you just leave the data in Excel and do the calculations there? If a nice report is what you're after, export just the results to Word.

 

[sabavno]

Are you saying that I would able to use SQL in Excel?

 

[plawrence]

You can use SQL in Excel to pull data from whatever your source is. But that's not what I was saying.

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

 

[sabavno]

The thing is every month user will pick two differenct spreadsheets and my goal is to match the spreadsheet and create the report of the unmatched records.

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.

 

[keyardee]

Hi,plawrence! I was not aware that we can use querries on excell data. How do we do it Any information or samle data is welcome

 

[plawrence]

sabavno, I'd check out John Walkenbach's Power Utility Pack at

http://j-walk.com/ss/pup/pup5/index.htm.

It does exactly what you're looking for with less headaches.

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