etting data from Excel into Access Please help (1 Viewer)

[TweakFreak]

Whats up everyone I am a fairly new user to access and I am trying to develop a database to hold quarterly data from multiple companies. What is the best approach getting the information off the companies data entry form into the database. The data entry form does change over time so I cannot reference specific cells. Can you use lookup tables in Access? Any help would be great.



Thank you all.



Mike

 

[reclusivemonkey]

I'm confused by this;

What is the best approach getting the information off the companies data entry form into the database.

A data entry form should put the information into the database. Thats its function. A data entry form that doesn't would be an odd beast indeed.

 

[TweakFreak]

Sorry to confuse you. THe data entry form is an excel file sent to clients. They input their information and send back via email. I am looking for a way to input this information into a db.

 

[nateobot]

The data entry form does change over time so I cannot reference specific cells.

It is going to very difficult to code something if you have no standards in place.

 

[TweakFreak]

The way the information is pulled now is I have assigned variables to each question (e.g., Age = AG) I use a lookup table to find AG and pull the value from the adjacent cell. This method allowed me to change the order of the questions if needed. I would like to something similar is Access. Is it possible?

 

[nateobot]

The way the information is pulled now is...

What application is pulling the data from the spreadsheet currently?

You can reference the Excel Library in your Access Application and use the Offset(StartingCell,0,1) function to get the cell next to your AG reference.

 

[TweakFreak]

The data is pulled from one excel workbook to another. Please explain how I can pull information into an access DB. Anyhelp would be great.

Mike

 

[Rickster57]

Excel

Have you tried using the docmd.transferspreadsheet ?

If you search this forum you will probably find plenty examples of this.