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

[daniels012]

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

 

[Joe4]

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.

 

[daniels012]

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]

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]

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]

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]

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]

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]

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!!
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]

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