[Charmian]

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]

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

[Charmian]

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]

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]

These links should help me - Thank you; I'll post again if its worked or I have other hassles.

[OBP]

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]

opened at OP request

[Charmian]

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]

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.