×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Spreadsheets and Access db

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.

RE: Spreadsheets and Access db

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.

RE: Spreadsheets and Access db

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

RE: Spreadsheets and Access db

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

RE: Spreadsheets and Access db

(OP)
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.

RE: Spreadsheets and Access db

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

RE: Spreadsheets and Access db

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources