Automating excel import and query

[bschultz]

I was wondering how to create a macro to automatically import an excel file that updates daily. Once that is imported I would like to automatically run a query without manually entering the joins and other steps associated. Any help would be greatly appreciated. Thank you

 

[dbDamo]

More info needed here.

Is the Spreadsheet to be imported into a new or existing table?

If it is an existing table (or even imported into a new table and then appended into an existing table) then there should be no need to edit any joins in an existing query.

What other steps are associated?

An autoexec macro will run automatically when a Database is opened (unless the DB is shift opened) so you could run from here.

I am assuming you will only want the import and query to be run once a day, so you would have to create a new simple table which captures the last time (date) the query was run and then perform a check in the autoexec macro to ensure it is not run on the same day and you would also need to update the last run date in the new table.

 

[bschultz]

Thank you for your response. It would be imported into a new table. These are the following steps i have been doing:

1. Go to File, Get External Data..., and choose import file.
2. Select the specific Excel File
3. I select a series of options.
4. Check the box for the option that indicates the First Row Contains Column Headings. You will get a soft error message about the format of the column heading. Click OK for now. Select the option to import the table into a New Table. Click Next on both the Field Options screen and the Primary Key screen.
5. Import as a new table with a similar name
6. Create New Query in Design View
7. Add the most recent imported excel file table and LookUp table to the query design.
8. I add specific Fields to the query
9. Create joins between both tables and save-

How would I set up the autoexec macro? Sorry I am relatively new to access. Thank you

 

[dbDamo]

Is it the same Excel file to be used every time?

If so, why can the data not be imported into the existing table?

What changes in the Spreadsheet? New records? Updates to existing records?

You are proposing to create a new table and query every day - pretty soon your DB will become somewhat difficult to manage...

 

[DCrake]

Then again why not simply link the spreadsheet and it will always be live.

David

 

[dbDamo]

The only problem with linking the Spreadsheet is that if the Linked Spreadsheet is being used in Access (eg as control source in a form that is open) then you are unable to open the Spreadsheet...

 

[bschultz]

The excel file that we will be importing will be a report that is updated daily with new records. I believe we will be using the same table everytime just importing that report daily into the table. Thank you

 

[dbDamo]

Ok, thats good as there appears to be no need to create a new table and query every day. All you need to do is get the data from your Spreadsheet into your table.

As I see it you have 2 options:-

1) You delete the records in the table then import the Spreadsheet data
2) You import the Spreadsheet data into a temporary table and append new records to the existing table

You will have to decide which option is best for you. When you have decided, then we can help you automate it.

Either way this should be very straight forward.

 

[bschultz]

Thank you for your prompt response. I believe the best option would be option 1. I will delete the records. Please let me know how to proceed.

 

[ghudson]

Forget the macros and use VBA..

CurrentDb().Execute "DELETE * FROM [YourImportTable]"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourImportTable", "X:\YourExcelFileToImport.xls", False

 

[dbDamo]

I was going to create a function and then run that function from an autoexec macro as deleting/importing are not the only actions to perform. He wants it to run automatically once a day the first time the Database is opened so more work to do.

bschultz - I have some code that can do all of this (most provided to me by bob larson) - I will just need to play around with it and bring it all together.

 

[dbDamo]

Ok, here you go.

Start by creating a new table with one field and make it a date datatype (formatted short date). In this example I have named my table 'LastAutoImport' and I have named the date field 'LastAutoUpdate' - obviously you can change these names.

Now open up VBA and create a new module. Paste this code into the new module:-

Function DailyAutoUpdate()
On Error GoTo DailyAutoUpdate_Err
If DLookup("[[COLOR=red]LastAutoUpdate[/COLOR]]", "[COLOR=red]LastAutoImport[/COLOR]") <> Date Then
CurrentDb().Execute "DELETE * FROM [[COLOR=red]YourTableToImportToHere[/COLOR]]"
DoCmd.TransferSpreadsheet acImport, asSpreadsheetTypeExcel9, "[COLOR=red]YourTableToImportToHere[/COLOR]", "[COLOR=red]X:\YourExcelFileToImport.xls[/COLOR]", [COLOR=lime]True[/COLOR] 
CurrentDb().Execute "UPDATE [[COLOR=red]LastAutoImport[/COLOR]] SET [[COLOR=red]LastAutoImport[/COLOR]].[[COLOR=red]LastAutoUpdate[/COLOR]]  = Date();", dbFailOnError
DoCmd.OpenQuery "[COLOR=red]YourQueryToRunHere[/COLOR]"
End If
DailyAutoUpdate_Exit:
    Exit Function
DailyAutoUpdate_Err:
    MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Function

The items highlighted in red will need to be changed to reflect your table/query names etc. The item highlighted in green will only need to be changed to False if your Spreadsheet does not contain column names.

Now create a new macro in design view. Select RunCode as the Action and type DailyAutoUpdate() into the Function Name argument. Save the macro as AutoExec.

Testing:-

1. Open your one field table and enter yesterdays date into the date field
2. Close your Database
3. Open your Database
4. Your query will open based on the new data in the table
5. Open your table and compare to your Spreadsheet to ensure new records have been added successfully
6. Open your one field table to ensure the date has been updated to show todays date
7. Close your Database
8. Open your Spreadsheet and add a new record, save and close
9. Open your Database
10. Your query will not open
11. Open your table and check to ensure the new record has not been added (will be added tomorrow or the next day the Db is opened)
12. Open your one field table to ensure the date has not been updated and still shows todays date

I have tested this in a sample Db I have created, but for some reason it won't let me upload. If you need to see it then I will try to upload it when I get home or put it on a file hosting site.

EDIT - able to upload now. Just need to make sure the Spreadsheet is extracted to C:\. I've set the LastAutoUpdate to yesterdays date already.

 

[ds_8805]

Hey dbDamo.. the code u provided works well.. I tried it in a sample database and it works perfectly fine. However when I try to implement the same code in the project database, one of my column is cannot be imported properly. Just some details on my project. I am trying to import four columns. The first one is a column with all numbers. I have set to text datatype and tried to import. This is the column that keeps giving in an type conversion failure. I have tried to change the datatype to number and it gives me the same error. ( When I tried to import the same 4 columns in my sample spreadsheet, I had set all the columns as text datatype and it worked perfectly fine). It absolutely neccessary for me to have that column as text type. Hope u can shed some light on what I might be doing wrong in the project database.

Any help would be greatly appreciated. Thank You.

 

[ghudson]

Import the file into a new table and see what data type Access assigns to the first column. Then use that table [rename it to what ever you need] as your new table to automate your importing.

 

[ds_8805]

Hey Thanks for taking your time to answer my question. Really appreciate it. I have now managed to partially solve my problem. Now I am able to import all the four columns like perfectly for the first time. However, when I try to import again, only very few rows are imported. Hope you can give me some suggestions on what I might be doing wrong.

Thanks

 

[ds_8805]

Sorry i just realised that even in the 2nd time, it does import .. However what happens is that the 2nd half of rows from the excel sheet is imported first and then there is quite a number of blank rows before the 1st half of the excel sheet is imported. However, I am quite keen that the excel sheet is imported as it is for easier reference. Hope u have suggestions on this! Thanks

 

[Vassago]

You could always do a query to delete any percieved blank rows. If the rows are not actually blank (null) on the spreadsheet, Access will import them.

 

[ds_8805]

Hey Thanks for ur help.I tried what u suggested. However, the problem still persists. However, now the first part of excel spreadsheet is imported at the top and there are blank rows and the 2nd half of the excel spreadsheet is imported. Is there a solution to this ?

 

[ds_8805]

Please ignore my previous post. How would u run a delete query automatically. After the spreadsheet has been imported, then I need to run the delete query which then deletes the blank rows. Would be possible to run this automatically?

 

[vid]

Hi!
I came across your code and used it. It was working fine in Access 2003. I have just upgraded to 2007 and everytime I open my database it shows the following error..


Could Not Find Installable ISAM 3170


Please Help!!!!! Thanks