Automating an Excel import Tony Williams 12/29/2008 2:37:02 PM Once a day we recive an Excel spreadsheet that contains data for certain (but not all) the fields in one of the tables of a database. I know that it is possible to import these using the wizard but I wondered whether there was any way to automate the process. For example, ideally I would like a command button that I clicked which opened up the Windows Explorer Browse window, allowed me to browse for the file and, when found, the data would be automatically added to the table when, say, the Explorer window is closed. I'm assuming that I would have to create an append query to add the data although some (but again not all) of the data would be added to existing records and some data could be new records. There will be a field in the Excel data that matches a field in the table. I'm not too hot on VBA code so if that is the root an ABC description of the steps would be appreciated. Many thanks Tony

Re: Automating an Excel import "pietlinden[ at ]hotmail.com" <pietlinden[ at ]hotmail.com> 12/30/2008 6:32:17 AM On Dec 29, 8:37 am, Tony Williams <TonyWilli...[ at ]discussions.microsoft.com> wrote: [Quoted Text] > Once a day we recive an Excel spreadsheet that contains data for certain (but > not all) the fields in one of the tables of a database. I know that it is > possible to import these using the wizard but I wondered whether there was > any way to automate the process. For example, ideally I would like a command > button that I clicked which opened up the Windows Explorer Browse window, > allowed me to browse for the file and, when found, the data would be > automatically added to the table when, say, the Explorer window is closed.. > I'm assuming that I would have to create an append query to add the data > although some (but again not all) of the data would be added to existing > records and some data could be new records. There will be a field in the > Excel data that matches a field in the table. I'm not too hot on VBA code so > if that is the root an ABC description of the steps would be appreciated. > Many thanks > Tony     You could use TransferSpreadsheet to import the data and use a saved import specification. Then you could use the OpenFile API to allow the user to choose an Excel file to import... See www.mvps.org/access/api .... and then the first article in the section.

Re: Automating an Excel import Tony Williams 12/30/2008 9:02:09 AM Hi thanks for that pointer. However as I mentioned I'm not too hot on VBA and this looks a little scary but I'll have a go at trying to understand what's happening in the code. Would you mind helping me along the path though? You say use the Transferspreadsheet to import the data and then the Openfile API, but how do I link them together? I've looked at Help on that action and I assume the data import code would be on the Onclick event of the command button but I'm struggling with how do I trigger the OpenfileAPI within that onclick event? Thanks for this, really appreciate your help. Have a great New Year! Tony "pietlinden[ at ]hotmail.com" wrote: [Quoted Text] > On Dec 29, 8:37 am, Tony Williams > <TonyWilli...[ at ]discussions.microsoft.com> wrote: > > Once a day we recive an Excel spreadsheet that contains data for certain (but > > not all) the fields in one of the tables of a database. I know that it is > > possible to import these using the wizard but I wondered whether there was > > any way to automate the process. For example, ideally I would like a command > > button that I clicked which opened up the Windows Explorer Browse window, > > allowed me to browse for the file and, when found, the data would be > > automatically added to the table when, say, the Explorer window is closed.. > > I'm assuming that I would have to create an append query to add the data > > although some (but again not all) of the data would be added to existing > > records and some data could be new records. There will be a field in the > > Excel data that matches a field in the table. I'm not too hot on VBA code so > > if that is the root an ABC description of the steps would be appreciated. > > Many thanks > > Tony > > You could use TransferSpreadsheet to import the data and use a saved > import specification. Then you could use the OpenFile API to allow > the user to choose an Excel file to import... See www.mvps.org/access/api > .... and then the first article in the section. >

Re: Automating an Excel import "Roger Carlson" <RogerCarlson[ at ]noemail.noemail> 12/30/2008 3:28:55 PM On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ImportSpreadsheet.mdb" which illustrates how to do this. It does a little more than you want to do, but if you look at the code behind the forms, you can trace what is happening. You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=339 -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L "Tony Williams" <TonyWilliams[ at ]discussions.microsoft.com> wrote in message news:E0D2E87E-4C97-49C5-8EAC-23A8CE8075E6[ at ]microsoft.com... [Quoted Text] > Once a day we recive an Excel spreadsheet that contains data for certain > (but > not all) the fields in one of the tables of a database. I know that it is > possible to import these using the wizard but I wondered whether there was > any way to automate the process. For example, ideally I would like a > command > button that I clicked which opened up the Windows Explorer Browse window, > allowed me to browse for the file and, when found, the data would be > automatically added to the table when, say, the Explorer window is closed. > I'm assuming that I would have to create an append query to add the data > although some (but again not all) of the data would be added to existing > records and some data could be new records. There will be a field in the > Excel data that matches a field in the table. I'm not too hot on VBA code > so > if that is the root an ABC description of the steps would be appreciated. > Many thanks > Tony

Re: Automating an Excel import Tony Williams 1/1/2009 10:05:00 AM Thanks Roger I'll have a look at your example. From past experience ofyour solutions I'm sure it'll be just what I want. being a VBA novice though I may be back for some guidance. Happy New Year! Tony "Roger Carlson" wrote: [Quoted Text] > On my website (www.rogersaccesslibrary.com), is a small Access database > sample called "ImportSpreadsheet.mdb" which illustrates how to do this. It > does a little more than you want to do, but if you look at the code behind > the forms, you can trace what is happening. You can find the sample here: > http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=339 > > -- > --Roger Carlson > MS Access MVP > Access Database Samples: www.rogersaccesslibrary.com > Want answers to your Access questions in your Email? > Free subscription: > http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L > > > "Tony Williams" <TonyWilliams[ at ]discussions.microsoft.com> wrote in message > news:E0D2E87E-4C97-49C5-8EAC-23A8CE8075E6[ at ]microsoft.com... > > Once a day we recive an Excel spreadsheet that contains data for certain > > (but > > not all) the fields in one of the tables of a database. I know that it is > > possible to import these using the wizard but I wondered whether there was > > any way to automate the process. For example, ideally I would like a > > command > > button that I clicked which opened up the Windows Explorer Browse window, > > allowed me to browse for the file and, when found, the data would be > > automatically added to the table when, say, the Explorer window is closed. > > I'm assuming that I would have to create an append query to add the data > > although some (but again not all) of the data would be added to existing > > records and some data could be new records. There will be a field in the > > Excel data that matches a field in the table. I'm not too hot on VBA code > > so > > if that is the root an ABC description of the steps would be appreciated. > > Many thanks > > Tony > > >