Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Help: example of reading excel in Access


(!)

Charmian's Avatar
Charmian Charmian is offline
Member with 179 posts.
THREAD STARTER
 
Join Date: Mar 2012
Location: South Africa
04-Jul-2012, 05:04 AM #1
Help: example of reading excel in Access
Hi
Does anyone have an example of how to read a xcel spreadsheet with multiple sheets line by line in VB? I want to read the data and populate a database or update fields in my database.
I have done the job manually; and the layout of the excels are always the same; but the job takes about a hour per branch to populate the db.
I want to automate the updating of the db; because the running of the queries takes seconds and saves the current person whose job this exercise manually at month-end DAYS of checking and creating the data.
This job so lends itself to automating; but I just don't know how to read the excels.

Thank you
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,605 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
04-Jul-2012, 06:09 AM #2
if the sheets names remain the same - you could just link to the excel sheets and then update from access using quries
my access is so rusty now - but that's how I would input and output to excel daily , weekly and monthly reports

so the new excel workbook - over writes the old sheet on the share/drive
open access and the links will pick up the new excel sheets - run the quires to update the master table or tables from the excel sheet and produce the reports

avoids any VB process on the excel - all done in access and could just run a macro in access to do all the manipulation if multiple queries involved
__________________
Wayne
Please let us know what the final solution was to any problem posted
Charmian's Avatar
Charmian Charmian is offline
Member with 179 posts.
THREAD STARTER
 
Join Date: Mar 2012
Location: South Africa
04-Jul-2012, 07:11 AM #3
Hi Etaf,
I don't how to link the excel sheets in access. I want to do it all from Access - so that with a click of a menu the reports can be run. I want to cut out the manual intervention. The excel spreadsheets are emailed to my user on a monthly basis. How do I link?
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,605 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
04-Jul-2012, 07:21 AM #4
Quote:
The excel spreadsheets are emailed to my user on a monthly basis. How do I link?
thats the same as I used to have to setup for some users

so first - save the excel spreadsheet into the same folder with the same name each month - so you over write
not sure how many spreadsheets or sheets you have

now on access you can set up a link to those external spreadsheet
see here

http://office.microsoft.com/en-us/ac...001095095.aspx

now use the Link option
http://office.microsoft.com/en-us/ac...#_Toc269881998
that way any changes to the spreadsheet data will be reflected in access

then you need to setup the queries

now each month - overwrite the spreadsheets and provided the spreadsheet name and sheet name and ranges are the same - the access linked table will show the new data

does that help at all
Charmian's Avatar
Charmian Charmian is offline
Member with 179 posts.
THREAD STARTER
 
Join Date: Mar 2012
Location: South Africa
04-Jul-2012, 07:51 AM #5
These links should help me - Thank you; I'll post again if its worked or I have other hassles.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,524 posts.
 
Join Date: Mar 2005
Location: UK
04-Jul-2012, 07:53 AM #6
The other course that you can take is to Import the Excel Sheets in to Temporary Tables and use Queries to update the real tables in Access.
You can also use TransferSpreadsheet to grab ranges in an Excel sheet and add it to a table.
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 51,605 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
05-Sep-2012, 04:31 AM #7
opened at OP request
Charmian's Avatar
Charmian Charmian is offline
Member with 179 posts.
THREAD STARTER
 
Join Date: Mar 2012
Location: South Africa
05-Sep-2012, 05:09 AM #8
Hi, sorry it took so long to get back to you. The link works, but when I write a query I get errors, due to the names used on the columns in xcel. e.g.
SELECT Milcar.[ Acc], Milcar.[Surname ], Milcar.[Init ]
FROM Milcar;
This is the sql produced from the design screen. The error I get is that it wants a " paramater Milcar. Acc entered. " I want to be able to use the xcel spreadsheet names, even if some have embedded spaces and some are right justified - it makes sense. How do I get rid of this error - I'm hoping its an 'add a bracket' answer.
Charmian's Avatar
Charmian Charmian is offline
Member with 179 posts.
THREAD STARTER
 
Join Date: Mar 2012
Location: South Africa
05-Sep-2012, 05:35 AM #9
Okay, It seems that if you take the table name out - it works! e.g.
INSERT INTO Customer ( accno, Cname, Cinitials )
SELECT [ Acc], [Surname ], [Init ]
FROM Milcar;
So go figure. Thank you
Won't mark as solved yet, incase I have more hassles over the next few days.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑