Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking cells in Excel to Access (2002 - XP)

    Hello all,

    I am having a devil of a time. I have years of data in Excel spreadsheets and would like to be able to manipulate the data in Access. I cannot figure out a good way to do it. I have tried the Excel feature "Convert to MS Access" but I just get the error message that the database cannot be created.

    A brief explanation of what I have and what I want:

    Have:
    Individual (by month) workbooks containing a spreadsheets for each day of each year for seven years. With one additional spreadsheet that consolidates all the totals from each day.
    Basic layout of the sheets has not changed from the beginning.

    Want:
    To link, import, something, the data from cells in the total (consolidation sheets) spreadsheets to cells in an Access database. The purpose of this being I want to create a sales forecaster. This forecast would be based on historical sales by day of week, with customer counts and daily temperatures.

    Is this an overwhelming thing? I have been copying and pasting from the Totals spreadsheets to a new spreadsheet and then I will just copy and paste from the new spreadsheet into an Access table. But there has got to be a way to do this automatically or with less time.

    TIA

    Bret

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: linking cells in Excel to Access (2002 - XP)

    I'm not clear on whether you want to convert the individual month spreadsheets to Access or only the totals. Do you still intend to keep the daily stuff in Excel or are you talking about switching over to an Access application for the whole thing?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    Hello Charlotte,

    Right now I would like to just use the totals. I don't really care that I convert the entire worksheets.

    Eventually I would like to move all of this to Access. I like the idea of being able to use the data that I collect and I have decided that I was wrong to use Excel for this purpose. It requires to much manual manipulation to be very useful. I need to create forms that resemble the Excel Spreadsheets we use so as to not create angst for my managers when they do their daily paper work.

    Thanks Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    Try importing into Access instead of exporting from Excel: create a new blank database, then use File | Get External Data | Import... to get the data into Access. To do this successfully, your Excel tables must either start in cell A1 of a worksheet, or have a name assigned to them.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    Hello Hans,

    When you say the the " or have a name assinged to them", do you mean, name the cell in Excel. For example instead of cell d4 give it the name "temperature"?

    The problem I have had with importing into Access is that the spreadsheets are laid out in the fashion of the original paper form that was filled out by hand every night. This puts label and data mixed together in columns and rows.

    Thanks Hans

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    By "name", I meant a name assigned to a range as a whole in Excel: select a range, then type the name in the cell address box in the formula bar, or select Insert | Name | Define...

    If you want to import data into Access, they must be in table form, i.e. rows = records and columns = fields. For example:

    <table border=1><td align=center>ID</td><td align=center>Company</td><td align=center>Product</td><td align=center>Price</td><td>1</td><td>Acme</td><td>Widget</td><td align=right>$9.98</td><td>2</td><td>Acme</td><td>Bolt</td><td align=right>$0.49</td><td>3</td><td>HAL</td><td>PC</td><td align=right>$1999.00</td></table>
    If the data is in another form, you will have to get it into table form first, or write code to process it while importing into Access.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    I think I am starting to see what you are saying. My "totals" spreadsheet is in columns and rows, the problem there has been the fact that I am using two or three rows at the top for the labels.

    I will try importing just the totals sheet. In the past have seen the other spreadsheets displayed on the import screen and I knew they wouldn't work.

    Thanks Hans

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: linking cells in Excel to Access (2002 - XP)

    You can name an entire range on a spreadsheet and it doesn't need to start at A1. Just start with the upper left corner of your totals data and name that entire range something like "TotalsData". Then you can import that range into Access. If you actually used two or three rows for column labels rather than wrapped cells, you will need to make sure the row closest to the data itself is given a unique name and that you include only that row in your data range. Otherwise, the labels will get imported as well and all your data will come over as text because of the format of the labels. Make sure you let Access know that the first row of your data range if field names or it will assign such useful names as Field1, Field2, etc.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking cells in Excel to Access (2002 - XP)

    Thanks Charlotte and Hans,

    This has been real helpful. Now I just need to spend some time and then later I won't have to dig quite so hard to find the answers.

    Bret

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •