Results 1 to 6 of 6
  1. #1
    joesephb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3

    Excel Sheet data import into Access

    Hi, I hope someone here can help me out.


    I have been trying to find info on this for a while with no luck. I am also very new to Access.
    I used to work for a company that did this so I know it is possible but I can't seem to find anyone online that has done anything similar.
    I work at a machine design company where each project has it's own excel file Bill of Materials.
    Each BOM excel file has multiple sheets that are directly exported from our design software.
    Each sheet is a sub-assembly of the project.
    Each sub-assembly sheet contains a list of all the Purchased AND Manufactured components.

    What I am looking for are two buttons I can add to the top of my sheet template.
    One button would be "Send to Purchasing" and the other would be "Send to Manufacturing".
    When the "Send to Purchasing" button is pressed, it looks for all items with a vendor field populated and sends that line entry to a access database called Purchased.
    Similar thing happens when the other button is pushed for the Manufactured items.
    In access I will have two master databases ("Purchased" and "Manufactured") that track all projects.
    These databases will allow me to do queries, reports, etc...
    Like I said, I used to use a similar system before and it worked great. So if anybody could help me with this or at least point me in the right direction, it would be greatly appreciated!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Mostly anything can be done with enough code. What you describe sounds rather complicated and sophisticated for a newbie. If your sheets are simple enough, set links and search as you would a native table. When you have code with a specific issue, post question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    joesephb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    Thanks June7,
    Maybe I can simplify things a little bit here.
    My thoughts are that In excel, I can still have each sheet as described in my original message with the buttons. But instead of having the buttons send info to the access databases, I would have them get sent to a "Purchased" sheet and a "Manufactured" sheet within the same excel file.
    Those sheets would have linked tables to access databases. So basically the only info that would be added to the databases are the info that is already sorted and separated and added to the two sheet tables.
    The only thing I can't figure out is how I could use my excel file as a template.
    Like I originally said, each project has their own BOM excel file with a unique filename.
    Instead of linking access to a specific excel file, is there a way to link an excel template to a specific access database?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Instead of linking access to a specific excel file, is there a way to link an excel template to a specific access database?
    IMHO, there would be no point. A template cannot hold any data, otherwise it is no longer a template. Plus, Access cannot modify a linked sheet, so that just adds to why I think that would be of no use. You can link specific sheets to Access, and if they are updated, Access sees the updates, but this is not what you're asking for. I also think this is an Excel question seeing as how you seem to be seeking knowledge on how to put buttons on a sheet. Again, just my opinion but I would forget that. If I was going to be adding or working with Excel data from within Access, I'd forget about programming too much on the Excel side. Maybe that's because I'm more familiar with that method; maybe it's because I've dealt with Excel and it's vba and the difficulties it presents with respect to protecting Excel data; especially if the workbook was shared.

    As June7 noted, just about anything is possible, but respectfully, this isn't a task for a novice.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    joesephb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    3
    When I say "template", I am referring to a file our designers would start with, rename and use to populate data into from the design software. for example, the file would be called "template.xlsm" and the designer would copy that into the jobs folder as "project_number.xlsm".
    You can link specific sheets to Access, and if they are updated, Access sees the updates, but this is not what you're asking for.
    In fact, that is very close to what I am asking for.
    Forget all the stuff about the buttons,, That's NO problem. I'm past that.. What I need to know is how to handle the problem about files changing names to the job numbers and still staying linked to the databases.
    The whole point of this is how to get data populated into excel by 12 mechanical designers who know nothing about Access into a database.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry if I missed the point, but this is what threw me
    is there a way to link an excel template to a specific access database
    That reads like you wanted to link the template itself.
    Once you know the location and name of this newly created file, in Access you can use Get External Data from the ribbon to link that file as an Access table. If you chose to import instead, there would be no link, but that's an option that the GED wizard presents to you. Beyond that, I guess you'd have to be more revealing about who's doing what and what level of automation you're looking for. I suppose an engineer can be quickly taught how to use that wizard? To make this more automated would require a fair bit of code writing - the more automation, the more code. When you get right down to it, Access could scan through a whole list of folders, grab each complete path, and link or import the file when no one is around. One thing you might want to consider is, sometimes linked Excel data presents data type conversion issues on the Access side, such as when you try to do math or sorting on numbers but they're really text.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-27-2016, 06:04 AM
  2. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  3. How would I import the excel sheet like this to Access
    By Arvine in forum Import/Export Data
    Replies: 3
    Last Post: 05-18-2014, 03:47 PM
  4. Import excel sheet data into Ms-access using VBA macros
    By gokul1242 in forum Import/Export Data
    Replies: 2
    Last Post: 10-02-2012, 04:39 AM
  5. Replies: 2
    Last Post: 08-14-2012, 04:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums