Importing Excel Files

[Kayleigh]

Quick question about linking spreadsheet - if I only require the data as a one of, does the spreadsheet always remain linked after data has been appended or do I explicitly need to unlink it?

 

[theDBguy]

Quick question about linking spreadsheet - if I only require the data as a one of, does the spreadsheet always remain linked after data has been appended or do I explicitly need to unlink it?

If you create a linked table, you'll have to delete it to unlink the data. There is a way to pull data from Excel without creating a linked table.

 

[Kayleigh]

Okay. I saw your code above, which I have adapted.
I would like the tables to be imported on a temporary basis - do you advise using a function for the user to delete the table when finished use or to store table?

 

[tmyers]

I know what I ended up doing was keeping the table as a temp table then one of the last operations that runs is a delete query that complete clears the table. My intent was to use the table multiple times however.

 

[Kayleigh]

Good idea but how do I select the required columns when importing. Bear in mind that different spreadsheets will be structured differently. I'm looking for First Name, Surname and Percentage. See the sample tables which have already been imported.

What I would like my system to do:

• Import necessary data from Excel spreadsheet into the specified columns
• Calculate grades for each row of imported table
• Export to new Excel sheet
• Side point - possible to change grade boundaries in form (done)

Attached database of my progress so far.

 

[tmyers]

I am not super technical, and DBGuy is well above and beyond me in knowledge. The process I used to set everything up was to use the import wizard to make the table for me when importing the excel sheet. That gave me my template to use. I then append only the data I actually want out of the temp table to a more permanent table. You could then export that table to Excel.

As far as the calculations, I can try and take a peak but at the end of the day I am not very knowledgeable.

 

[theDBguy]

Okay. I saw your code above, which I have adapted.
I would like the tables to be imported on a temporary basis - do you advise using a function for the user to delete the table when finished use or to store table?

If you use the code I posted here, you could add the following line just above the TransferSpreadsheet line to make sure you work with a fresh set of data.

DoCmd.DeleteObject acTable, "TableName"

Hope that helps...

 

[Kayleigh]

Don't worry about the calcs - already sorted those. Just basically looking for best way to do the import at the beginning.

My queries are all set up. So how do I go about appending specific columns to table. I am trying to do as much as possible that the user can manipulate data on form without me needing to use advanced functions... (happy to use code behind so long as it is simple for user to do on front end)
Problem is that data is imported with different number of sheets, columns and column titles so how do I get user to select required columns/sheets and assign to correct column in my temp table?

 

[tmyers]

If the columns you are importing change constantly, then that is beyond me and what I have done. I will fully differ to DBGuy.

 

[Pat Hartman]

We need a better understanding of your process. Are you importing the same spreadsheets every week? How many variations do you have? Are the repeats always the same name as the previous versions or does each workbook have a unique name? In order for us to help you to automate this, we need to understand if there is a pattern.

 

[Isaac]

You need to create one well-worded, coherent thread/question for each actual thread or question. This seems like a mishmash of a vague and changing series of different questions. Can you come up with one CLEARLY DESCRIBED scenario/question and post accordingly?

 

[Kayleigh]

Yes literally just about to do that. I will start a new thread...