Import Excel Spreadsheet into Access

[icaines]

For starters I have very little experience with Access, however I have the daunting task of importing a spreadsheet into a table within Access. This would be a simple task except for the fact that the spreadsheet contains field names that are not included in the table in Access. In addition, the field names are not located in column headers in the spreadsheet but are located to the right of their corresponding values. I have no clue how to go about solving this problem and changing the format of the spreadsheet is not an option. I attached a cropped screen shot of part of my dilemma. Any help would be greatly appreciated.

Thanks,

 

[plog]

I checked your screenshot and thought 'Oh, that sucks', then I read this:

...and changing the format of the spreadsheet is not an option.

And then I chuckled a little as I realized that you are screwed for some time to come. What that sentence means to me is that this will be an ongoing process, not a one time import. If you needed to do this once just to get your data into Access it would be a pain, but at least you would be done and could move on. Your process sounds like it needs to be repeated.

This isn't really an Access issue, this is file-prep issue. Access needs a file with columns and rows, that's all there is to it. So you need to do something outside of access to create a file that you can import into Access.

That could be manually copying and pasting the data into a file that Access can import, or creating some sort of Excel VBA process that runs through every line of your spreadsheet and extracts the data you need to a new tab in a row/column format. The Excel/VBA solution would make life easier, but it's not a beginner-level task. You said you aren't great with Access, how is your Excel/VBA/Macro skills? I'm guessing maybe a little above your Access skills, because otherwise you would have done this already in Excel. So, I think you may be in for a manual process.

Now the big question, why? Why does this data need to be stored in Access? Why does the input form need to be in Excel if it doesn't play nice with where you ultimately want your data?

 

[icaines]

Thanks for your response. To make things more clear as it pertains to the why.. I'm not too sure on the exact reasons behind it because I'm working on this for another dept. but what I do know is that some information is kept in Access I presume for the benefits it provides such as reports, queries, etc. and probably other reasons as well, on the other hand the info. has to be entered into this excel spreadsheet I'm guessing because it's simply the protocol.

Therefore, in an endeavor to eliminate double typing the idea was to see if there was an easy way to import the data into the database.

Thanks again, I'll try the solutions you mentioned.

 

[The_Doc_Man]

This is the way I might do it. I will use terms that you can look up on line to see various ways to do each thing.

1. In the module (class or general) where you want to do this dirty deed, create your function (if you want do this via Macro) or subroutine (if you can do this from a form.) Either way, it will be some type of procedural code. So let's call it your procedure.

2. The procedure must create an Excel Application object. (Look up Create Application Object)

3. Use the methods of the object to do an OpenWorkbook to the file you are going to import. (From Excel help or with the great Google brain, look up Excel OpenWorkbook method.)

4. Activate the desired worksheet. (Look up Excel, Activate Worksheet)

5. Using ActiveSheet (which is a shortcut usable once a worksheet has been activated), you can now do direct access to the cells of the sheet.

For example, ActiveSheet.Columns(3).Rows(2) would point to worksheet cell C2.

6. Open a recordset for the table you want to receive your data. Look up OpenRecordset as an Access database topic.

7. Assuming you are going to insert one record, do a recordset .AddNew function.

8. Now use the random access of the ActiveSheet object to fill in the fields of the Recordset object.

9. When done, do an .Update on the recordset. and then CLOSE the recordset.

10. Close the Excel workbook with the option to NOT save it. (Look up the Excel workbook .Close method and check into the options, one of which is to silently close the workbook without changes).

11. For every object you loaded using a SET objvar = name-of-object, remember to set that object variable to Nothing. (That's a keyword in Access meaning "empty object")

You are done.

 

[GinaWhipp]

This was asked and answered here...

http://answers.microsoft.com/en-us/...ess-2010/b36a9fd6-015e-49ea-b067-9121538915e6

 

[GaryPanic]

would it not be easier to transpose the excel sheet

so sheet/tab one set up as you have it
sheet /tab 2 transpose the data or manual edit the date

Sheet 1 column A row 1 = name =Sheet 2 column A row 1
Sheet 1 column A row 2 = nwhateverA =Sheet 2 column B row 1
Sheet 1 column A row 3 = nwhateverB =Sheet 2 column c row 1
etc
its a little messy to start - but should resolve itself thereafter

then you just import sheet 2