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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,522
Office Version
  1. 365
Platform
  1. Windows
Sure. It can be done manually, with a Macro, or with VBA.

Have a look at the TransferSpreadsheet Action in Macro/VBA. In a Macro, it has good help about all the necessary arguments.
The nice thing about Macros, is that they are easily converted to VBA, where you can make the process more dynamic. After you create an example Macro that does what you want, just go to the Macro ribbon and click "Convert Macros to Visual Basic".

Note that both Macros and VBA can be assigned to Form Buttons.
 
Last edited:

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Ok, now that I know it is possible can I find out how to write VBA to do what I want.
Joe, you said look at TransferSpreadsheet Action. Where is that?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,522
Office Version
  1. 365
Platform
  1. Windows
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.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

Ah, I think you are saying this is from the spreadsheet?
I need a button on the Database. Is this possible?
Unless of course you are referring to the database?

See we have tons of already created spreadsheets where we have done estimates.
I have one database that i would like to get information from them.
So in reality I would like to....
Hit a button on an Access Form
Open a dialog to chose which workbook to open
Find the worksheet called "Estimating"
and do something like:

rs.Fields("Customer") = Range("B5").ValueThank you for your quick responses!!I have done Excel for a long time. Access I am a little "green" to be honest.</pre>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,522
Office Version
  1. 365
Platform
  1. Windows
No, this is all from the Database. Access has Macros too! The Transferspreadsheet Action is what you use to import Excel information into Access, or export Access information to Excel.

The nice thing about using Macros in Access is that you do not need to know any VBA at all. Just fill in the arguments. The caveat, is it is a little limiting in what you can do (kind of like using the Macro Recorder in Excel versus writing VBA code directly). However, once you create a simple example of what you want to do with the Macro, you can convert it to VBA code, which gives you a good starting point.

Don't be afraid to play around with it a little and do Google searches on "Transferspreadsheet". There is a lot of information out there on it, and you may even be able to find some tutorials.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

Ok, I tried this on a test database before I did it on my 'real" database. The only question I have is my form uses data from 3 different tables in a query. If I am transferring a name, address, and zip etc. etc. How can I test during the transfer if this name already exists of if it needs to create a new name in the "address table"?

Thank you
Michael D
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,522
Office Version
  1. 365
Platform
  1. Windows
If I am transferring a name, address, and zip etc. etc. How can I test during the transfer if this name already exists of if it needs to create a new name in the "address table"?
I am not quite clear on what you are asking. Are you referring to "Unmatched Queries" (that is, locating records in one table that do not exist in another)?
If so, Access has a Query Wizard for that to walk you through the process.
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Joe,
Thank You for your reply.
I have data on a spreadsheet. I want that data to be filled in on a new record in a table in my database.
The only issue I see... The table "RequestTbl" I am filling has a relationship to another table called BillToTable. This table is where we have our list of Customers.
So when I copy my data from my Excel sheet to my database table "RequestTbl" I may have a new customer or use an existing one.

So all that being said I need some help with how to code this!!:eek:
I need to click a button and this happen
Find Customer name in cell B5 and find out if there is a matche in the BillToTable
If there is pick that customer.
If there is not then create a new customer and use it.
Then proceed to fill in the rest of the sheet based on the information from the spreadsheet.

Is that a little clearer?
I am really inexperienced when it comes to Access.
Thank You,
Michael
 

Joe4

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

I would strongly caution about getting too deep into Access without having a good fundamental knowledge of it. If you do not have a good understanding of relational databases, and the rules of normalization, you can program yourself in a corner real quick (trust me, that was my first experience with Access). Its one thing to do a simple compare or query in Access, it is quite another to complete a project that performs various different tasks. Access is not as intuitive as Excel, and there usually is no "winging it" on an Access project.

One of the most important things when creating an Access program is your Table design. Design it correctly, according to the rules of data normalization, and you will be able to do almost anything you want. Design it incorrectly, and it will drive you insane.

I would strongly recommend going one of two ways:
1. Learn about the basics of Access, Relational Databases, and Rules of Normalization, then tackle your project.
2. If you are pressed for time, or don't have the desire to learn Access right now, your best bet might be to look for consulting services.

Here is a good link on some of the important topics I mentioned: Fundamentals of Relational Database Design
 

Watch MrExcel Video

Forum statistics

Threads
1,127,144
Messages
5,622,993
Members
415,944
Latest member
loribear180

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