Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 31st, 2006, 11:30 AM
Registered User
Join Date: Aug 2006
Location: Los Angeles, CA, .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing spreadsheet data with more control

I was wondering if there was a way to import a single line of an excel spreadsheet and then refer to the individual columns, in order to overwrite certain values of an existing record, but not all. I imagine I will need to get the spreadsheet row first into a temporary variable before I write some of its values to the database. If there is another way to do the following please let me know.

Here's the situation
Access table with existing data:
fields: PartNumber, Manufacturer, ConfigDate
PartNumber is unique, but is not the primary key.

Excel Spreadsheet:
columns(fields): PartNumber, Location, ConfigDate

I want to import the spreadsheet one line at a time and overwrite "ConfigDate" for the record in the database with the PartNumber corresponding to the one in the spreadsheet.

I'm pretty new to Access so excuse any of my incorrect terminology. Any help would be great, thanks.

Reply With Quote
  #2 (permalink)  
Old September 1st, 2006, 06:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

I would import the entire spreadsheet in to a second table.
Then you can code the Update.
Either create an update query if that will do it, or write code to open a recordset on each table, and parse through the records one at a time.

So it seems what you want to do is take data from Excel, and then where the PartNumber matches the PartNumber in the table, to overwrite Manufaturer and ConfigDate?

Reply With Quote
  #3 (permalink)  
Old September 1st, 2006, 10:30 AM
pjm pjm is offline
Authorized User
Join Date: Jul 2006
Location: Boston, MA, USA.
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts

Here's a snippet of some code that I used to read results from an Excel-based questionnaire
into an Access database.

    Dim rs As Recordset
    Dim xlWb As Excel.Workbook
    Dim xlWs As Excel.Worksheet

    Dim xlsFileWithPath as string
    Dim FieldName as string
    Dim Refs as string

        Set xlWb = xlApp.Workbooks.Open(xlsFileWithPath)
        Set xlWs = xlWb.ActiveSheet

        rs(FieldName)= xlWs.Range(Refs).Value & ""

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
importing spreadsheet with field mappings skrj02 Access VBA 9 January 11th, 2017 12:48 PM
create calendar from spreadsheet data iacon Excel VBA 1 February 10th, 2011 01:53 PM
Export Table data into an Excel SpreadSheet hewstone999 Access VBA 0 March 3rd, 2008 07:00 AM
SSIS: Use filename as data when importing data slesicki SQL Server 2005 0 August 7th, 2006 05:00 PM
Spreadsheet control for Netscape 4.7 pankaj_daga ASP.NET 1.0 and 1.1 Basics 3 November 27th, 2003 08:16 PM

All times are GMT -4. The time now is 10:05 AM.

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