Import Excel into Access

CJones

CJones

Final Approach
Joined
Mar 14, 2005
Messages
5,785
Location
Jawjuh
Display Name
Display name:
uHaveNoIdea
I have an Excel workbook with a growing number of worksheets. I would like to be able to import each worksheet into Access as its own table. I can do this manually via the File -> Get External Data... and import each worksheet individually.

Is there a way to import the entire workbook and have the individual tables created automatically? I don't mind if the current tables are overwritten each time the workbook is imported - once the tables are created, I will run a macro of some sort to do some tweaking of the data anyway.

Do I need to sit down and crank out some VBA to do this, or will Excel or Access do this automatically?

Access/Excel 2003.
 
Vba. I've got some code already written that does this.
 
I don't use Access.....

That said, you can import directly into SQL Server 2005 and above from Excel, so I'd imagine that you can also import into Access without VBA.

If you can't, that's yet another reason to not use Access.
 
SkyHog said:
I don't use Access.....

That said, you can import directly into SQL Server 2005 and above from Excel, so I'd imagine that you can also import into Access without VBA.

If you can't, that's yet another reason to not use Access.
Click to expand...

Forms and reports are easier to make in Access than SQL. :D
 
I've been running my Access database since 1997 with no issues that I haven't created myself.
 
Want even more fun with Access??

We're using Access as a user interface to pull from an AS400 system. :)
 
If anyone ever needs code to import worksheets from an Excel workbook and create a table for those worksheets, here is some VBA code I put together. Thanks to EdFred for getting me started down the right path.

Code: 
Dim objXL As Object
Dim sFile As String
Dim i As Integer
Dim strSQL As String
Dim strWSname As String

sfile = "C:\excelfile.xls"

Set objXL = CreateObject("Excel.Application")
objXL.workbooks.Open sFile

With objXL[INDENT] For i = 1 To .sheets.Count[INDENT] strWSname = .sheets(i).Name 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblTmp", sFile, True, strWSname & "!"

strSQL = "SELECT * INTO " & strWSname & " FROM tblTmp"
CurrentDb.Execute strSQL

strSQL = "DROP TABLE tblTmp"
On Error Resume Next
CurrentDb.Execute strSQL
[/INDENT]Next
[/INDENT]End With
objXL.workbooks.Close sFile 
objXL.application.quit
Set objXL = nothing
 
Last edited:
what are you ever going to do if they limit internet access at work?
 
tonycondon said:
what are you ever going to do if they limit internet access at work?
Click to expand...

Get a new job. :D

Maybe I'll get one of those cushy "Aerospace Engineer" gigs. I hear they're pretty much a cake-walk. ;)
 
flyingcheesehead said:
I guess he'll have to start knowing what he's doing. ;)
Click to expand...

If you show me an employee that knows how to do EXACTLY every aspect of his/her job without ever having to ask questions, I will show you an employee that is simply treading water and never looks for new ways to swim upstream.

I knew how to import Excel into Access - I had done it NUMEROUS times over the past several weeks. BUT, I was looking for a better way to do it. In the end, I did find a way to do it better - now I can import any Excel file with an indefinite number of worksheets with an indefinite worksheet names and it does it on-the-fly. Considering that I am trying to set up an inventory management system to manage some 20,000 distinct parts, the simple fact that I don't have to go through the Access wizard to import Excel files will allow me more time to focus on more important aspects of the project.... and more time to reply to asinine posts on PoA saying that I don't know what I'm doing because I ask questions.

Sorry Kent, you're my bud and all, but I take a bit of offense to your comment. But we can hash it out over Crown-and-Coke's at 6Y9. ;) :D
 
CJones said:
If you show me an employee that knows how to do EXACTLY every aspect of his/her job without ever having to ask questions, I will show you an employee that is simply treading water and never looks for new ways to swim upstream.

I knew how to import Excel into Access - I had done it NUMEROUS times over the past several weeks. BUT, I was looking for a better way to do it. In the end, I did find a way to do it better - now I can import any Excel file with an indefinite number of worksheets with an indefinite worksheet names and it does it on-the-fly. Considering that I am trying to set up an inventory management system to manage some 20,000 distinct parts, the simple fact that I don't have to go through the Access wizard to import Excel files will allow me more time to focus on more important aspects of the project.... and more time to reply to asinine posts on PoA saying that I don't know what I'm doing because I ask questions.

Sorry Kent, you're my bud and all, but I take a bit of offense to your comment. But we can hash it out over Crown-and-Coke's at 6Y9. ;) :D
Click to expand...

Meh, in my eyes, all the googling in the world can't teach you to program. But it can help you figure out that one thing that stumped ya every now and then.

I'd be lost without google, honestly.
 
Hey Chris,

You don't know what you are doing. :D
 
CJones said:
LOL That's it, I'm gonna have to bring the BIG beat-down stick to 6Y9!
Click to expand...

Just bring your BELT and lay into 'em! (ok, it's a lanyard, but still...)

Crown_Royal_Purple_Lanyard.jpg


That's still better than hitting them with your purse:

2288613.jpg
 
CJones said:
If you show me an employee that knows how to do EXACTLY every aspect of his/her job without ever having to ask questions, I will show you an employee that is simply treading water and never looks for new ways to swim upstream.

I knew how to import Excel into Access - I had done it NUMEROUS times over the past several weeks. BUT, I was looking for a better way to do it. In the end, I did find a way to do it better - now I can import any Excel file with an indefinite number of worksheets with an indefinite worksheet names and it does it on-the-fly. Considering that I am trying to set up an inventory management system to manage some 20,000 distinct parts, the simple fact that I don't have to go through the Access wizard to import Excel files will allow me more time to focus on more important aspects of the project.... and more time to reply to asinine posts on PoA saying that I don't know what I'm doing because I ask questions.

Sorry Kent, you're my bud and all, but I take a bit of offense to your comment. But we can hash it out over Crown-and-Coke's at 6Y9. ;) :D
Click to expand...

Better to ask questions while constantly growing your skill set then to not ask questions and just implement it in whatever half-ass way you can think of. I would much rather someone know less, while knowing how to ask questions, then someone knowing more thinking they don't have to ask.

I know a lot of stuff quite well and I am constantly asking questions. There is always someone smarter than me and more specialized than me in a certain area and I would much rather get their input before I waste my companies resources by traveling the wrong road.

Almost everything I do is powered by open source technologies. It is my personal policy to answer two questions for every question that I ask.

I would suggest you consider your Access usage carefully before committing. Access can work for some things -- but for most larger corporate solutions it is a giant waste of time that someone will have to consider even more time undoing. EdFred will of course say it works for him -- but he is a small company with few users. It doesn't scale beyond that very well at all. If you have multiple users that will be using it simultaneously please consider another solution (even more critical if it involves multiple offices).
 
Last edited:
jesse said:
I would suggest you consider your Access usage carefully before committing. Access can work for some things -- but for most larger corporate solutions it is a giant waste of time that someone will have to consider even more time undoing. EdFred will of course say it works for him -- but he is a small company with few users. It doesn't scale beyond that very well at all. If you have multiple users that will be using it simultaneously please consider another solution (even more critical if it involves multiple offices).
Click to expand...

Not my decision - it is already written in stone. The company I'm working for this summer (a LARGE international organization, by the way) has been using Access forever as a query interface to access an AS400 system (easier than writing queries in the 'green screen').

One of the guys I am working with was creating a growing list of parts in Excel. I needed to import that growing list of parts into Access so I could combine it with corporate production and inventory information. The VBA script, as I have it today, takes about 5-10 minutes to perform all the queries and data manipulation I want it to do and results in about 1gig of data.

Gotta use the tools you have available at the time. It's definitely not the optimal solution, but so far it has gotten the job done.
 
Sorry Kent, you're my bud and all, but I take a bit of offense to your comment. But we can hash it out over Crown-and-Coke's at 6Y9. ;) :D
Click to expand...

Hehe... You *DID* see the ;) didn't you? ;)

CJones said:
If you show me an employee that knows how to do EXACTLY every aspect of his/her job without ever having to ask questions, I will show you an employee that is simply treading water and never looks for new ways to swim upstream.
Click to expand...

A-freakin'-men to that. I even managed to learn more about driving trucks from other drivers at my last job, and occasionally even from trainees! (Did you know that you can get a buffet, to go? I had no idea! :blush::rofl:)

I knew how to import Excel into Access - I had done it NUMEROUS times over the past several weeks. BUT, I was looking for a better way to do it. In the end, I did find a way to do it better - now I can import any Excel file with an indefinite number of worksheets with an indefinite worksheet names and it does it on-the-fly. Considering that I am trying to set up an inventory management system to manage some 20,000 distinct parts, the simple fact that I don't have to go through the Access wizard to import Excel files will allow me more time to focus on more important aspects of the project.... and more time to reply to asinine posts on PoA saying that I don't know what I'm doing because I ask questions.
Click to expand...

Well, I like to say that laziness is the mother of invention. :D And the whole reason I'm such a gadget and computer freak is that I like to come up with new ways of automating things so that I don't have to do them any more. :yes:
 
CJones said:
Not my decision - it is already written in stone. The company I'm working for this summer (a LARGE international organization, by the way) has been using Access forever as a query interface to access an AS400 system (easier than writing queries in the 'green screen').

One of the guys I am working with was creating a growing list of parts in Excel. I needed to import that growing list of parts into Access so I could combine it with corporate production and inventory information. The VBA script, as I have it today, takes about 5-10 minutes to perform all the queries and data manipulation I want it to do and results in about 1gig of data.

Gotta use the tools you have available at the time. It's definitely not the optimal solution, but so far it has gotten the job done.
Click to expand...

To check my understanding -- The data ultimately ends up being stored on the AS/400 and Access is just a layer of accessing it?

If so, the above, makes a little sense...as Access isn't actually the storage location of all the data.
 
jesse said:
To check my understanding -- The data ultimately ends up being stored on the AS/400 and Access is just a layer of accessing it?

If so, the above, makes a little sense...as Access isn't actually the storage location of all the data.
Click to expand...

I'm not 100% sure how it works - I'm not a database guy and DEFINITELY not an AS400 guy.

All I know is that we are using some sort of IBM go-between driver to let Access hook up with the AS400 on which our MRP and MPS systems are housed. As I understand it, the 'Access' tables that we use are basically linked tables to the AS400 system. It looks and behaves like Access as far as queries/reports/forms/etc, but the data is actually being pulled from the AS400 system.

For my 'personal' data, I am pulling from the 'big' database and putting it into my own database. My 'personal' database which is full of data that I have queried from the 'big' database, is approximately 1-1.5 GB in size. I have no clue how big the 'big' database is because it is constantly being updated with live data.
 
CJones said:
I'm not 100% sure how it works - I'm not a database guy and DEFINITELY not an AS400 guy.

All I know is that we are using some sort of IBM go-between driver to let Access hook up with the AS400 on which our MRP and MPS systems are housed. As I understand it, the 'Access' tables that we use are basically linked tables to the AS400 system. It looks and behaves like Access as far as queries/reports/forms/etc, but the data is actually being pulled from the AS400 system.

For my 'personal' data, I am pulling from the 'big' database and putting it into my own database. My 'personal' database which is full of data that I have queried from the 'big' database, is approximately 1-1.5 GB in size. I have no clue how big the 'big' database is because it is constantly being updated with live data.
Click to expand...

Anybody running this app on their laptop?

This is EXACTLY how 2 million SSN's get lost when a laptop gets stolen... a local database storing a disconnected copy of a production mainframe/Oracle/SQL database.
 
TangoWhiskey said:
Anybody running this app on their laptop?

This is EXACTLY how 2 million SSN's get lost when a laptop gets stolen... a local database storing a disconnected copy of a production mainframe/Oracle/SQL database.
Click to expand...

Fortunately, there are only a handful of us that have access to the database itself. Everyone else has to contact the 'database guy' for our department and tell him what info they want and he will run the queries for them and send them along via Excel.

Also, you must have a valid AS400 logon to be able to access the data itself, so even if you have the 'Access' linked database on your laptop, you can't even open a table unless you have a valid AS400 logon.

AND, none of the 'personal' information is hosted on the database we have access to - just inventory and manufacturing information.
 
if that's really how things go there (not questioning you) then the DBA is fostering and promoting disparate data islands, which ultimately end up in:

"I've got the data"
"No, I'VE got the data!"
"You're data is wrong - mine is correct!"
"No, MY data is correct! Yours is out of date."

all fun and games until someone goes to jail for mis-reporting on your EPA SARA and RICRA reports ... or financial statement ... or tax forms ...

but then, it's ALWAYS the DBA's fault! :)
 
gkainz said:
if that's really how things go there (not questioning you) then the DBA is fostering and promoting disparate data islands, which ultimately end up in:

"I've got the data"
"No, I'VE got the data!"
"You're data is wrong - mine is correct!"
"No, MY data is correct! Yours is out of date."

all fun and games until someone goes to jail for mis-reporting on your EPA SARA and RICRA reports ... or financial statement ... or tax forms ...

but then, it's ALWAYS the DBA's fault! :)
Click to expand...

I don't know about the 'financial' data - where it's stored or how it's maintained. *Hopefully* there is better maintenance than what I am working with - the data redundancy across tables is ridiculous!

The DBA for our group is good about making sure everyone realizes that the data he gives them is simply a snapshot for a certain time frame. The few of us that have direct data access and can actually create our own queries are good at clarifying that the data we present is only a snapshot as well. Luckily, the group I work with is focused pretty heavily on 'factual' data as opposed to 'predicted' data, so there isn't much opportunity for people to be playing from different decks of cards.

Apparently it has worked well for them..... so far. I'm only here for the summer, so I don't have to worry about 'fixing' it, just 'dealing' with it. ;)
 
CJones said:
AND, none of the 'personal' information is hosted on the database we have access to - just inventory and manufacturing information.
Click to expand...

Yeah, the personal data is probably on some guy's laptop somewhere. :rofl:
 
You must log in or register to reply here.
Back
Top