Any chance of some help importing sheet from excel into Access using VBA

[shabbaranks]

Hi Guys,

I see its possible to import a spreadsheet into Access, but what I am trying to do is import a perticular sheet into Access. Also again if possible I would like to import only the fields which contain certain text - is this possible? As not all the data held within the sheet is necessary.

Thanks
shabba!!

 

[Alansidman]

Start here. http://msdn.microsoft.com/en-us/library/aa220766(v=office.11).aspx

If you are using a newer version, then go the the MS Access KB for instructions.

 

[shabbaranks]

Cool and thanks. Can I put that within

Private Sub Form_Load()

so it imports the data on loading of the form?

 

[shabbaranks]

Just to elaborate on this as I dont think I have given all the necessary info. Currently the combo box is populated from a table in which I imported the data from the spreadhseet choosing what data/cloumn etc I wanted to import. Does the above solution work within a combo box scenario and how do I now link the table using the above sample so it has the required spreadsheet data?

Thanks

 

[Mr. B]

You might want to try defining and then using an "import specification" to do the import. Take a look a this article:
http://office.microsoft.com/en-us/a...operation-as-a-specification-HA001226307.aspx

 

[shabbaranks]

Thanks Mr.B but from my understanding that requires user intervention I would like to automate this as much as possible so maybe on form load it imports or checks to see if there are any additions to import before or on load. Im sure there must be a way...

 

[Mr. B]

Your concept of this functionality is somewhat limited. You are correct that you will have to initially define the "Import Specification" by manually performing the import using the import Wizard. As one of the last options during the import process, you will have the opportunity to save your import process, giving it a name. The name you provide will be the name of your "Import Specification". Once you have defined and saved the import specification, you can then use the same Docmd.TransferSpreadsheet VBA code to import your data and you can use your named import specification in place of the "TableName" option of the TransferSpreadsheet" method.

 

[shabbaranks]

Ive tried as you suggested but I cant see how I get the import to append any new entries. At the moment it just imports everything again.

Any ideas?

Thanks

 

[Alansidman]

If the spreadsheet is continually being updated and you only want new data, then I have a couple of suggestions.

1. You could link your Excel spreadsheet to the Access Database. This then would give you all the data in your spreadsheet but you indicated that some was extraneous. The extaneous data could be ignored if this is a solution that works for you.
2. Alternatively, you could set your VBA code to delete the current table data and have the code import the entire spreadsheet into your table. This would give you specifically what you want.

 

[gblack]

Another thing you can do is create a Data table that you will append to.

Everytime you need to append a new set of data, you'll run the import spec (basically replacing the import table) then use that import table to append your data table using an Append Query.

Something like:

Docmd.SetWarnings False
Docmd.OpenQuery("Your Append Query Name here")
Docmd.SetWarnings true

 

[shabbaranks]

Is it possible to specify criteria on this? So I would only import the data which contains say "live data" in the "current status" column? Or would I import all the data then filter out what I want people to see within the combo box?

 

[Mr. B]

Now that things are a bit more clear as to exactly what you are trying to do, it seems that you are going to have to use a two step process. You can still automate the process but you will need to first just import all of the data that is available for import. Import this data into a "temporary" hold table. Then create a "select" type query that will extract the data rom the temporary holding table that you want appended to your actual table. When you have this query returning the fields and records you need appended then you can just change the query to an "append" type query, providing it with the name of the table to append the records to and making sure that all of the fields in the query are mapped to the appropriate field in your actual table. Once you have run the "append" type query and added the record from the temporary table to your actual table, then you create a query that will delete all records from the temporary table.

You can automate this with VBA code that will do the import, run the "append" query and then run the "delete" query.

 

[shabbaranks]

Im not too sure if this is achievable but thought I would ask the question. Is it possible to link a new table to the spreadsheet so the data within that table is always up to date then some how (maybe using a select type query??) import whats different between the 2 tables based on the criteria set. I was jut thinking this method would make sure the data within the holding table is always up to date?

Thanks again for your help on this

 

[Alansidman]

If you link your Excel table to Access, then you can run any number of queries to extract what ever information you want that is housed in the Excel table. No need to import as the data is already available in Access through the linking. Once you have the data in Access, what do you want to do with it?

Alan

 

[shabbaranks]

Currently I have a combo box which lists projects, to start with I just have a table. But the finished product needs the table to be updated from a spreadsheet, which occasionally gets exported from a different application and will be imported to Access. Im trying to work out the best dynamic method where by it doesnt require the user to import every time..

 

[Alansidman]

You have told us what is happening before you get the data into Access. You did not answer this question

Once you have the data in Access, what do you want to do with it?

 

[shabbaranks]

I thought I did in one of my earlier posts? The data from the spreadsheet populates a combo box in which a user makes thier selection. Once selected along with other inputs they then submit the form selctions to a table.

Just to elaborate on this as I dont think I have given all the necessary info. Currently the combo box is populated from a table in which I imported the data from the spreadhseet choosing what data/cloumn etc I wanted to import. Does the above solution work within a combo box scenario and how do I now link the table using the above sample so it has the required spreadsheet data?

Thanks

 

[Alansidman]

I think I understand it now. The excel table is the record source for a combo box on a form in your database.

Instead of importing, just link the excel table to your db. Create your combobox and make the record source the linked table. In the QBE in your set up for the combo box, only bring the fields you need for the combo box onto the grid. Follow the normal procedure to set up the combo box, ie. bound fields, number of fields, field length, etc.

This should work.