# Knoobe Question About VBA in Access - How do I tell it to import a file? **URL:** \ **Category:** Factual Questions\ **Created:** [April 18, 2007, 9:11pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682 "2007-04-18T21:11:21Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Engywook](https://avatars.discourse-cdn.com/v4/letter/e/bc8723/32.png) [@Engywook](https://boards.straightdope.com/u/Engywook)\ **Post date:** [April 18, 2007, 9:11pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/1 "2007-04-18T21:11:21Z")
Subject line says it all, or most of it. I’m interested in learning to use Access, and there’s nothing like doin’ if you’re up for some fast larnin’. What I’d like to do is build a form that automates a rather tedious task that nobody seems to want to do. Specifically, the Access database should do the following: 1. Allows the user to click a button to browse for and import an Excel spreadsheet to an Access table. 2. At the click of another button, a series of SQL queries are executed to make the table usable. 3. The desired, much smaller table is spat out (as a matter of fact, it’s two tables, but I don’t anticipate any trouble in this area). I know (almost) exactly how to do steps 2 and 3, and the SQL queries are all ready to go and working perfectly. I’m hitting a snag on step 1, though, and am having trouble finding a sample of VBA code that would do step 1. Is it even possible? Has anyone done this before, or know where I can find info on this? Once this is done, a task that used to take the better part of a day for most people would be completed in roughly 30 seconds. Like I said, I’m very new at this, and I’m not doing this because I’m considering a career as a developer down the road. But I would like to be able to leave something useful behind after my consultant position ends. ---
**Author:** ![Alwrong](https://avatars.discourse-cdn.com/v4/letter/a/94ad74/32.png) [@Alwrong](https://boards.straightdope.com/u/Alwrong)\ **Post date:** [April 18, 2007, 9:42pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/2 "2007-04-18T21:42:12Z")
[QUOTE=Engywook] I know (almost) exactly how to do steps 2 and 3, and the SQL queries are all ready to go and working perfectly. I’m hitting a snag on step 1, though, and am having trouble finding a sample of VBA code that would do step 1. Is it even possible? Has anyone done this before, or know where I can find info on this? [/quote] Try the DoCmd.TransferSpreadsheet method using acImport as the TransferType. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [April 18, 2007, 10:31pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/3 "2007-04-18T22:31:43Z")
In addition to the DoCmd transfer, you should probably take a look at [this](http://www.mvps.org/access/api/api0001.htm); it does the API window to allow your user to select the file. (Otherwise you have to somehow ensure that your user correctly names the file to an exact filename each time.) There’s an example at the top to show you how to call it. Set a variable to call the API, so that the variable contains the file name & path. Then you can use that variable in your DoCmd line. I’ve just done several of these (although using delimited files rather than Excel), so I’m game for questions if you have specifics. Don’t forget to include cleanup routines - are you appending data, or is it a separate set of data for each run? If it’s a separate set, you’ll need to clean out old data. If you’re cleaning out old data, do you want to archive it or just dump it? And error-checking. Don’t forget the error-checks, or some user will end up off in the wild blue yonder without a boat or a paddle. ---
**Author:** ![Engywook](https://avatars.discourse-cdn.com/v4/letter/e/bc8723/32.png) [@Engywook](https://boards.straightdope.com/u/Engywook)\ **Post date:** [April 19, 2007, 6:23pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/4 "2007-04-19T18:23:05Z")
Thanks, guys, I managed to track down an import sub from what you’ve said, and it seems to be working. I haven’t tried getting it to open the browse window yet… that code seems deeper, and I’m still trying to wrap my head around it (I don’t generally try to use code in any language until I understand what it’s doing). But I will figure it out… and perhaps I’ll be back with another problem I can’t solve. ---
**Author:** ![Engywook](https://avatars.discourse-cdn.com/v4/letter/e/bc8723/32.png) [@Engywook](https://boards.straightdope.com/u/Engywook)\ **Post date:** [April 19, 2007, 7:34pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/5 "2007-04-19T19:34:12Z")
Well, as I thought, I’m back. I’ve also got some formulas to put tags on each row. I really wish I didn’t have to do this in Excel, but I don’t see a simple way to do this step in Access. First formula is for a field called **Order**. As it sounds, it increments by 1 for each row: =a2+1 Second formula is for **sessid**. It looks for a zero in another field which indicates the end of a session, and assigns an id for each session: =if(h2=0, b2+1, b2) Third formula decides whether the session’s a keeper. It looks for a magic number and makes a note if we’ve got a winner: =if(m2=34567812, “keep”, “”) All pretty simple, but I guess Access requires a non-simple approach. I can’t really imagine a way to do this in SQL, and again, I don’t really have quite the vocabulary needed to get what I need out of Google to do this in VBA. Is this do-able (even if I’m admittedly using the wrong ap), or am I going to have to open that 20meg Excel file at some point after all? ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [April 19, 2007, 8:00pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/6 "2007-04-19T20:00:50Z")
[QUOTE=Engywook] _First formula is for a field called **Order**. As it sounds, it increments by 1 for each row:=a2+1_ [/quote] Is the Order already set, or are you trying to set it? Access tables have a datatype “autonumber” which will assign consecutive numbers to your records. (You’d just create the column “Order” and set the type to “autonumber”.) Is that what you’re trying to do? > [@](#): > > _Second formula is for **sessid**. It looks for a zero in another field which indicates the end of a session, and assigns an id for each session: > =if(h2=0, b2+1, b2)_ This sounds like your db structure is off; probably the session info should be split into a separate table. (I’m assuming there’s multiple sessions per one _something_ in your data.) However, given that you’ve got a table with column Session (B) and column EndOfSession (H), you can do the same thing in Access. ```auto UPDATE YourTable SET YourTable.Session = IIf(YourTable.EndOfSession=0,YourTable.Session+1, YourTable.Session); ``` > [@](#): > > _Third formula decides whether the session’s a keeper. It looks for a magic number and makes a note if we’ve got a winner: > =if(m2=34567812, “keep”, “”)_ Same thing. ```auto UPDATE YourTable SET YourTable.Keep=IIf(YourTable.KeeperNote=34567812,"keep",""); ``` Does that do what you need? ---
**Author:** ![EllisDee](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ellisdee/32/4531_2.png) [@EllisDee](https://boards.straightdope.com/u/EllisDee)\ **Post date:** [April 20, 2007, 2:53am UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/7 "2007-04-20T02:53:09Z")
[QUOTE=Engywook] I’ve also got some formulas to put tags on each row. I really wish I didn’t have to do this in Excel, but I don’t see a simple way to do this step in Access. [/QUOTE] I wouldn’t go so far as to call it _simple_, but it’s not that hard to automate Excel from within Access VBA. There’s a nice tutorial explaining how to do that here: > **[Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)-VBForums](https://www.vbforums.com/showthread.php?t=391665)** > > Do you want to create or read an Excel file from your program? > > Perhaps you want to create a report with some data, a few formulas, and some graphs, or maybe get some particular data out of a spreadsheet? > > The good news is that Excel has been... That board is a more suitable place for getting help on your project in general. ---
**Author:** ![Engywook](https://avatars.discourse-cdn.com/v4/letter/e/bc8723/32.png) [@Engywook](https://boards.straightdope.com/u/Engywook)\ **Post date:** [April 20, 2007, 5:10pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/8 "2007-04-20T17:10:12Z")
Thanks for the tips… I’ve checked in with vbforums and given them a more detailed description of my problem. Got a feeling I’ve got a trip to Barnes and Noble in the near future, though. ---
**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\ **Post date:** [April 20, 2007, 11:11pm UTC](https://boards.straightdope.com/t/knoobe-question-about-vba-in-access-how-do-i-tell-it-to-import-a-file/400682/9 "2007-04-20T23:11:27Z")
Good luck with your project! I’ve actually gotten kind of fond of Access VBA programming, in an odd sort of way, except when it’s driving me nuts. About 50/50, I’d say. 😛