Button (VBA) to import Excel worksheet data into a table

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
We have a form that we fill out in excel it has a bunch of information for a job. Customer, numbers, job descriptions, etc. etc. All of this is on an Excel Spreadsheet. I just this would help tremendously in preventing transposing/misspelling of numbers and names and other items.
Is it possible to import this information into my Database?

I can explain a lot more with more details if needed. I just wanted to know first if it is possible.

Excel worksheet = GreenSheetInfo.xls
Access Database = WorkOrder.mdb
Database Table = RequestTbl
Note: Field names do not match. But the cells for each item on the Excel Spreadsheet will be in the same place every time!

Thank You,
Michael Daniels
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I will gladly share the database with you. It is too large to send even zipped. I think i have it set up correctly... I think!
Or let you login and look at it through a "Google+ hangout" or "teamviewer".

Thank you for all your responses so far.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
I am trying to encourage you to learn about all these topics before you dive too deeply into your project. If you do not have a good understanding of these concepts before designing your database, you are going to most likely have a very difficult time. There is far too much there for me to teach you in a few short posts. It is important to do this upfront work if you want to create a well-designed database.

BTW, unfortunately, I do not do any consulting work myself. I wish I had the time, but outside of work I coach two softball teams (those kids keep me pretty busy!).
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Joe,
I understand totally!
I love the fact of you coaching. I coached both of my boys all through the years. I now umpire baseball and I coach at our local high school.
Thank you
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
I love the fact of you coaching. I coached both of my boys all through the years.
Its been a lot of work coaching 2 teams (4-5 days a week), but it is rewarding when you see how much they improve during the year!
 

CaptJamesCook

New Member
Joined
Aug 18, 2014
Messages
1

ADVERTISEMENT

Create -> Macro and select the Transferspreadsheet Action.
The arguments are pretty self-explanatory, and using F1 which each one will give you expanded help and examples.

When finished, you can convert to VBA if you like, using the method I described in the first reply.

This looks too easy. However, I'm using Access 2010 and I don't have "Transferspreadsheet" or any operation starting with "T*" listed in my Macro operations.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
This looks too easy. However, I'm using Access 2010 and I don't have "Transferspreadsheet" or any operation starting with "T*" listed in my Macro operations.
They are hidden by default. Click on the "Show All Actions" button on the "Show/Hide" ribbon on the Design Menu.
 

cplmckenzie

New Member
Joined
Aug 18, 2014
Messages
13
Michael,

Just looking at your thread. A couple of questions.

1. Will this be an update (adding more records) of an existing Access table or will this be a complete overwrite of records in the table.

2. this relationship between tables RequestTbl and BillToTable is based on a key field , such as CustomerIdNumber.....or....

cplmckenzie
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Michael,

Just looking at your thread. A couple of questions.
Not sure if you realized, but the posts you are referring to are over a year old!
This was an old thread that CaptJamesCook resurrected yesterday.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,743
Members
417,165
Latest member
Hilders1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top