Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 17th, 2004, 04:18 PM
Registered User
 
Join Date: Sep 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Access data from excel sheet

Hi,

I am currently trying to convert my excel spreadsheet which is a product list in which I update the prices from multiple supplier prices regularly. Being a spreadsheet it is difficult to work with, so I am looking for a way that I can import the main products list, then periodically, update 2 or 3 fields in each product from the excel pricelist received from suppliers.

If anyone has any ideas it would be most appreciated.

 
Old October 20th, 2004, 01:51 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I had to do this from an information supplier for awhile.

If there is only one excel spreadsheet and it is updated (and you have to use an excel spreadsheet) then just link your access database to the spreadsheet and treat it as a linked table. You will have to save it where the access file can always see it. If there are multiple users, then put it on a network drive that everyone has mapped the same.

When you want to update it, just replace it with the new one with the updates while the database is closed.

If it has a different name each time, then just make sure you rename it the same as the linked table name.

And make sure you don't mess around adding new fields to the spreadsheet.

If you get different files from each supplier, then just link to all of them seperately instead of trying to consolidate and or convert them each time.

If you want to keep this info in the access database, then just delete the table, and reimport the new one. As long as the field names and values are the same, access won't mind. It will even add its own PK for you each time. I saved the table structure as a template table, and then would copy and paste the template table with the name of the old table, and then reimport the data from the new file.

I had to update several tables each month, and refer to older data, so I wrote buttons that would delete the current month's tables, copy, paste and rename template tables, then import the data for the month that was wanted. I did this all from a network folder containing only excel files.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
access function in data sheet(another sheet) jani Excel VBA 1 May 21st, 2008 07:15 PM
Excel sheet accessing(edit/update) in asp.net hemant.dna ASP.NET 1.0 and 1.1 Basics 0 January 31st, 2007 07:37 AM
Import Data from Open Excel Sheet to Access chintu4u Pro VB Databases 0 May 15th, 2006 01:24 AM
access data of excel sheet into sql server in asp KGANESH2006 SQL Server ASP 2 April 20th, 2006 02:33 AM
Exporting data from MS Excel sheet to Ms Access ajindal General .NET 1 January 17th, 2005 03:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.