Append data from Excel to Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 9 of 9

Thread: Append data from Excel to Access

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Append data from Excel to Access

    [Originally posted by lokiloki]

    The data I need to import is in a Excell xls that holds the data for each Column , in the first row of the spreadsheet.
    I have tried TransferSpreadSheet and this does not work.

    Tried to open a connection to the xls spreadsheet in the code but I get the "Provider not found" even though I have all the correct references and Jet.OLEDB.4.0
    installed.

    I am trying to get the data from the xls to the database table. Each time the project is run, a new excel spreadsheet is created and this data must be appended to the database, so it can be run on seperate systems.

    I have no problems creating the database with fields, but need to set column widths through VB code.

    Any help would be Appreciated!
    THX
    I am running VB6 and Office 2000 SP2

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Append data from Excel to Access

    [Originally posted by Andon K.]

    There are two ways, one that you already tried, that is to open Excel as a database. You got the error probably because in the connection string you didn't include the following string:

    "Extended Properties=""Excel 9.0;HDR=Yes;"";"

    (That works if you have headers in your worksheet.) The syntax for referencing worksheets as database tables is a bit strange. For example, you shold have:

    Set rsExcel = New ADODB.Recordset
    rsExcel.Open "SELECT * FROM `Worksheet Name$`", ...

    The worksheet name must end with a $ character and all must be enclosed between two ASCII 96 characters. Also, I am not 100% sure, but I think in this way you can use Excel files only in readonly mode.

    The alternative is to open Excel through Automation using the CreateObject or GetObject functions. In the MSDN disks and on this and other sites you will find many examples of such code.

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Append data from Excel to Access

    [Originally posted by lokiloki]

    Thanks, I found some code in my Programming Access 2000 book that will link them. This works but I would rather write to the database.
    Here is the code I found to link, do you know how I can change this to import or write to the database ?
    When I ran the code it placed the data on the first row of the database, is their a way to have it placed on the second brow ? The first row is fields only.

    THX !!
    I will try it your way.

    Function LinkExcelTable() As Boolean
    Dim dbs As DAO.Database, tdf As DAO.TableDef

    Const errNoISAM As Integer = 3170
    Const conPath As String = "C:\DiskBoot\Data\SystemInfo.mdb"

    On Error GoTo Err_LinkExcelTable
    ' Return a reference to Northwind database.
    Set dbs = OpenDatabase(conPath)
    ' Create new TableDef object.

    Set tdf = dbs.CreateTableDef("SystemInfo")
    ' Specify range that is source table.
    tdf.SourceTableName = "A2:AQ2"
    ' Specify connect string.
    tdf.Connect = "EXCEL 8.0; DATABASE=C:\DiskBoot\Data\" + Form1.Text2.Text + ".xls"
    ' Append new TableDef object.

    dbs.TableDefs.Append tdf
    LinkExcelTable = True
    MsgBox "Database and Table created..."
    Exit_LinkExcelTable:
    Exit Function

    Err_LinkExcelTable:
    If Err = errNoISAM Then
    Dim strErr As String
    strErr = Err & ": " & Err.Description
    strErr = strErr _
    & "You may not have the ISAM driver installed properly on your computer, " _
    & "or you may have specified the Connect string incorrectly." _
    & " Check the Connect string and the ISAM driver."
    MsgBox strErr, vbOKOnly, "Error!"
    Else
    MsgBox "Error " & Err & ": " & Err.Description
    End If
    End Function


  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Append data from Excel to Access

    [Originally posted by Andon K.]

    This a third possibility for importing data from Excel to Access.

    This code just links the Excel worksheet to the database, so in the database you "see" it as just another table. (Try to execute this code and then look at the tables in the Access Database window. You should see a table name with a small Excel icon indicating that now you have linked an external Excel worksheet).

    Then you can use a standard technique for copying data from one table to another in the same database. For example, you can use an SQL Select Into statement, or an Insert stament, or you can do it "by hand", i.e. open a Recordset on the Excel table and then looping through all its rows copy them one by one to another table.

    HTH.

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Append data from Excel to Access

    [Originally posted by lokiloki]

    I got it to work using TransferSpreadSheet.
    Now I need to figure out how to delete the empty rows in the Access table. If you know how, please let me know. Thank you for all your help...you lead me down the right path...

    PS I have a form for the Access table, if you have any ideas on how I can populate it with the data I'm transfering...that would be great.


  6. #6
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Re:Append data from Excel to Access

    [Originally posted by Andon K.]

    TransferSpreadSheet is OK if you work with Access 2000, but you don't have this method in earlier versions.

    As for deleting rows, before importing data, you can run a Delete query to empty the table from old data.

    To populate your form with data, you must first set the Record Source property of the form to the appropriate table or query. Import data in that table and then open the form.

    HTH.

  7. #7
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Re:Re:Append data from Excel to Access

    [Originally posted by lokiloki]

    THX
    The data is appending like I want, the empty rows are showing up on the top of the access table.

    Every time it's appended, it creates a empty row on the first row of the table.
    So if the project is run 3-4 times, their is 3 or more empty rows and my form has empty records until I use the control to move to where the records start.

    As for showing the form, I tried DoCmd.OpenForm "name of my form"
    but the form only "Flashed" on the screen and went away.
    THX again !

  8. #8
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Re:Re:Re:Append data from Excel to Access

    [Originally posted by Andon K.]

    Are you sure the first row of your spreadsheet is not empty? Do you have column names headers in your spreadsheet (you should). As far as I know, one argument of this method is for specifying whether the source spreadsheet has column headers or not. Maybe you set this argument incorrectly.

    I don't have experience with the TransferSpreasheet method (old Excel 97 habits die hard ...)

  9. #9
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:Re:Re:Re:Re:Append data from Excel to Access

    [Originally posted by lokiloki]

    I have column headers but I am only transfering the second row of the spreadsheet(has the data)
    Could be that it's transfering both because I have the same coloumn fields in the database it's tranfering to. It would append the database and create an empty row where the fields would be.

    THX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center