p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Importing spreadsheet data with more control (http://p2p.wrox.com/showthread.php?t=47238)

tencelos August 31st, 2006 11:30 AM

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.


mmcdonal September 1st, 2006 06:54 AM

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?


mmcdonal

pjm September 1st, 2006 10:30 AM

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 & ""


-Phil-


All times are GMT -4. The time now is 02:27 PM.

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