exporting data to access table from excel

[ecowar]

I have an Excel spreadsheet set up for my job where people fill out a data entry section, which in turn fills out all of the supporting documentation through formulas. What I want to do is to be able to send/update certain key data from my excel spreadsheet to an access database table each time a particular macro is run to keep track of key production elements for future reference. I know it's probably easier than I think, but I would appreciate any help or suggestions you might have.

 

[Corticus]

Welcome!

The first step is to put your caculated values that are in Excel into a format that Access will recognize as being fields, and records, with fields in row 1, and the records going down 1 line per record. You might have to create a new sheet to combine your spreadsheet data into this format.

Then, in Access, open your DB and select File|Get External Data|Link Tables.

Change the file type to .xls, and select your spreadsheet. The wizard should walk you through finding your table, and if it has been organized as described above, Access should have no problem recognizing it as such. Complete the link, and a table should pop up with the XL icon.

The XL doc will need to be open for Access to view the records, but once linked, Access will immediately update values from the XL spreadsheet to the table, who's data you could then incorporate into your DB using queries.

HTH,

 

[ecowar]

Corticus,
I was able to link the table from my spreadsheet, OK. It reads the data like I want. Thanks. Now the problem I'm running into is whenever the spreadsheet is changed, access only reads new data if the file is saved rather than appending new data to the end of a table when a macro is run. I'm trying to do this in steps, as I get very little time to work on this "project".

Another problem I'm going to have is that the users fill in the data, run my print macro to print all the necessary pages required, and then give me the hard copies so that I can process them. Usually the excel spreadsheet is not saved so creating another sheet within the file is not really an option. Even if copies of it are saved by the user, they are scattered over 20 or 30 possible servers and many of the users say, "Access??? Do I have that??". That's why I was hoping to be able to append a database file from within my print macro. Do you think I would be better off staying within Excel and if so can you point me in the right direction?

Many, many thanks in advance.
BH

 

[Corticus]

Hmmm...

Now I'm a little confused(so what else is new). Let me see if I can clear up your problem before I try to figure out how to fix it.
It seems like your saying that you have a lot of users, each with their own version of a spreadsheet. You want the new data from each person's spreadsheet to append a table in your one Access database when they execute the print macro. Is this correct?

Corticus

 

[ecowar]

Corticus,
That is correct. Each user has their own copy of the same spreadsheet. It didn't work out having a single copy of the Excel file. There were just too many users. I've been making slow progress tweaking and streamlining towards this point, but I got stuck. You have summed up my current goal for this project in one nice neat statement. Sorry if I wasn't clear before.

BH

 

[ecowar]

Corticus,
I was thinking. I could probably add a sheet to each file which consolidates the data I want captured from the main sheet, like you mentioned earlier. But, I'm kind of stuck on how to append that data to a single access database and not just keep overwriting the existing data in the table. Basically, I want to capture key details from each request submitted to me so that I can keep track of this data.

 

[Corticus]

Good!

You should certainly be able to accomplish this. I've got a few more questions but I will still be working on a solution while I'm waiting for the answers, they will just help clear a few things up.
Do you know in advance how many people will have this spreadsheet?
You mentioned about 20-30 users, would you have control of the distribution of these sheets, or could the users duplicate them and Distribute them further without your prior knowledge?
How will you receive this data, through e-mail, via a common network, or some other means?
I'm asking, because I think the easiest manner of dealing with this would be through Access's consolidating data tool. You could distribute a db with each spreadsheet. Each db would only be linked to the spreadsheet with which it was distributed. Access will then allow to consolidate many dbs into one, with little fuss.

HTH,

 

[Corticus]

Okay,

I think the method I was suggesting earlier should work for you. I would make one spreadsheet, and one database. Put a sheet on the spreadsheet that consolidates the data into a format that the db recognizes. You could make that sheet only update when the print macro is executed if you want.
Then select Tools|Replication|Create Replica.
This will create a replica of the db which you will be able to consolidate later. As you need to distribute the spreadsheet, copy it and keep everything named the same, and include with it the replicated db. You could then have your users send you these replicated dbs however often they have to, and through Access's replication tool, combine all the data. Unfortunately, I don't know much about replication, and its 5:00 so I have to go home, but if Help doesn't give you any, post back and I'll see what I can figure out.

good luck,