Importing from Excel

[brett429]

I know this is probably a stupid question, but I'm not sure if it can be done. I want to be able to import Excel spreadsheets and have Access extract information from only specific columns for reporting. I'm able to do this with Queries and then run reports based off those Queries.

However, the table data is going to change. One week I might be working with one set of Excel data and the next week it may change. The only thing I can think to do is keep the Queries and Reports in the database and then delete the tables and import the new Excel data to new tables and rename those tables to exactly what the old tables were called (so the Queries and Tables will still run). All the columns will have the same names, but the data in the rows will change... Is there a more automated way I can do this w/o having to go in and delete and re-add tables of new data?

 

[TylerTand]

If you use a make table query, everytime you run the query it will first erase the old one and replace it with a new table with the new data. This would fix your problem of having to have a table with the same name for other code or queries. You could put the whole procedure behind a button and have it update the tables with new data and then run the queries.
docmd.runsql "Make Table Query Here"
docmd.runsql "Select Query here"

Hope this gives you some ideas.

Tyler

 

[brett429]

If you use a make table query, everytime you run the query it will first erase the old one and replace it with a new table with the new data. This would fix your problem of having to have a table with the same name for other code or queries. You could put the whole procedure behind a button and have it update the tables with new data and then run the queries.
docmd.runsql "Make Table Query Here"
docmd.runsql "Select Query here"

Hope this gives you some ideas.

Tyler

Hi Tyler,

Thanks for the info. I'm still fairly new to Access programming, so if you could elaborate a little I would REALLY appreciate it. I created the make table query and based it off the existing query I already had. Is that OK? I created a button on a form and input the code you gave me for the onClick event, however, it's erroring because I apparently need more than just the Query names in those quotations. What else do I need to do? And I assume that the user would still need to important the raw data from Excel each time before the Query is run, correct?

Basically, if I could just have it to where the user imports the data to a new or existing table... and can just click a button on a form that will automatically run the query to filter out the fields I want and generate it all to a report, that's EXACTLY what I need!

 

[cpremo]

You're a little confusing with regards to your initial statement.

First, will the column names always be the same? Will the type of data in each of the columns always be the same (i.e., Column1 will alway be Names, Column2 will always be dates, etc)?

If the data type will constantly be changing, then I don't see how you can use a "canned" query. If however, your data type will always be the same then you should be able to just: 1.) Clear the import table, 2.) Import your data, and 3.) Run your Reports.

Also, will you be comparing this data to another data table or are you just running reports against the data in the imported spreadsheet? If your are comparing and the type of data in each column changes then you're back to 'can't use "canned" queries'.

Can you post a "non-sensitive" copy of your Excel data?

 

[brett429]

You're a little confusing with regards to your initial statement.

First, will the column names always be the same? Will the type of data in each of the columns always be the same (i.e., Column1 will alway be Names, Column2 will always be dates, etc)?

If the data type will constantly be changing, then I don't see how you can use a "canned" query. If however, your data type will always be the same then you should be able to just: 1.) Clear the import table, 2.) Import your data, and 3.) Run your Reports.

Also, will you be comparing this data to another data table or are you just running reports against the data in the imported spreadsheet? If your are comparing and the type of data in each column changes then you're back to 'can't use "canned" queries'.

Can you post a "non-sensitive" copy of your Excel data?

I'm not sure if I can post a "non-sensitive" copy of the data, but I will be more specific about what I'm doing.

Several times a month we receive Excel spread sheets filled with raw data. The column names are ALWAYS the same, but the data in the rows is different in each version of the spread sheet. Many of the columns contain blank rows or data we don't need to look at. Right now what we're having to do is go through and hide columns, format the spread sheets, and adjust it in other ways that makes it more presentable. This can be very lengthy to have to do each and every time we get a new spread sheet. My idea was to create a Query that would extract only certain columns of information that would then be run into a report to make the data look presentable.

I know there's probably no way around having to import the data using Access's "Import Excel" method. But I'd like to avoid having to delete/clear tables repeatedly. I'd like for this to be easy enough for anyone on my team to do. Just import the data from Excel, click a button, and boom - it generates the report.

I appreciate the help!!!

 

[cpremo]

Actually the import command is extremely simple:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "AccessTableName", "Path:\Directory\Filename.xls", True

Now as to the data. Again if the type of data in each of the columns always be the same (i.e., Column1 will alway be Names, Column2 will always be dates, etc) you won't have much of a problem. However, if the data in the columns constantly changes, you're in a real difficult position.

Blank rows are no problem since you can create a query to delete blank rows based on the criteria you set. Just use a statement like this:
DoCmd.OpenQuery "Delete - Blank Rows"

with "Delete - Blank Rows" being the name of the query.

Once imported, do your cleanup there. Just create your queries and use the "DoCmd.OpenQuery" command to run them in sequence. Then, delete the imported data when you're done with a delete query.

If you want to communicate off-Line, just send a message - cpremo_JUST-ReMove@Remove-Before.mbc.ca.gov

 

[brett429]

Actually the import command is extremely simple:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "AccessTableName", "Path:\Directory\Filename.xls", True

Now as to the data. Again if the type of data in each of the columns always be the same (i.e., Column1 will alway be Names, Column2 will always be dates, etc) you won't have much of a problem. However, if the data in the columns constantly changes, you're in a real difficult position.

Blank rows are no problem since you can create a query to delete blank rows based on the criteria you set. Just use a statement like this:
DoCmd.OpenQuery "Delete - Blank Rows"

with "Delete - Blank Rows" being the name of the query.

Once imported, do your cleanup there. Just create your queries and use the "DoCmd.OpenQuery" command to run them in sequence. Then, delete the imported data when you're done with a delete query.

If you want to communicate off-Line, just send a message - cpremo_JUST-ReMove@Remove-Before.mbc.ca.gov

Sorry for the delay. I'm still working out the kinks, but your idea did work. Thanks for the help!