1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help: example of reading excel in Access

Discussion in 'Business Applications' started by Charmian, Jul 4, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    179
    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.:eek:

    Thank you
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,945
    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
     
  3. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    179
    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?
     
  4. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,945
    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/a...ata-between-access-and-excel-HP001095095.aspx

    now use the Link option
    http://office.microsoft.com/en-us/a...cess-and-excel-HP001095095.aspx#_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
     
  5. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    179
    These links should help me - Thank you; I'll post again if its worked or I have other hassles.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  7. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,945
    opened at OP request
     
  8. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    179
    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.
     
  9. Charmian

    Charmian Thread Starter

    Joined:
    Mar 6, 2012
    Messages:
    179
    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.
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1059620