Importing Excel to Access - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Importing Excel to Access

darthmoobeydarthmoobey Posts: 140Member
Hello everyone, I have been reading up on importing cells and data from an Excel spreadsheet into an Access Database. Every Cell will pretty much be a field in the database. It is a small program for a friend of mine to take the data he had in an excel spread sheet, so he can querry and run reports from. There have been so many methods I have read to do this, but none were clear enough to make me think it was the fastest since it will be cell to field importing. I have coded importing from text files, using the Mid(STRING) and Left(STRING) methods, but I figured with excel it would be much different. Can someone please toss me a link, or maybe some example code for quick importing a spread sheet and seperating cell into field. Example:

Cell A1 = First Name | Field FirstName
Cell A2 = Last Name | Field LastName

Something like that.

Thanks alot
The darthmoob

Comments

  • dokken2dokken2 Posts: 532Member
    : Hello everyone, I have been reading up on importing cells and data from an Excel spreadsheet into an Access Database. Every Cell will pretty much be a field in the database. It is a small program for a friend of mine to take the data he had in an excel spread sheet, so he can querry and run reports from. There have been so many methods I have read to do this, but none were clear enough to make me think it was the fastest since it will be cell to field importing. I have coded importing from text files, using the Mid(STRING) and Left(STRING) methods, but I figured with excel it would be much different. Can someone please toss me a link, or maybe some example code for quick importing a spread sheet and seperating cell into field. Example:
    :
    : Cell A1 = First Name | Field FirstName
    : Cell A2 = Last Name | Field LastName
    :
    : Something like that.
    :
    : Thanks alot
    : The darthmoob
    :


    You can directly import the worksheet from the file/get external data menu. To programmatically import, see online help for the TransferSpreadsheet/TransferDatabase methods.

  • darthmoobeydarthmoobey Posts: 140Member
    : : Hello everyone, I have been reading up on importing cells and data from an Excel spreadsheet into an Access Database. Every Cell will pretty much be a field in the database. It is a small program for a friend of mine to take the data he had in an excel spread sheet, so he can querry and run reports from. There have been so many methods I have read to do this, but none were clear enough to make me think it was the fastest since it will be cell to field importing. I have coded importing from text files, using the Mid(STRING) and Left(STRING) methods, but I figured with excel it would be much different. Can someone please toss me a link, or maybe some example code for quick importing a spread sheet and seperating cell into field. Example:
    : :
    : : Cell A1 = First Name | Field FirstName
    : : Cell A2 = Last Name | Field LastName
    : :
    : : Something like that.
    : :
    : : Thanks alot
    : : The darthmoob
    : :
    :
    :
    : You can directly import the worksheet from the file/get external data menu. To programmatically import, see online help for the TransferSpreadsheet/TransferDatabase methods.
    :
    :

    Thanks Dokken, I looked into the TransferSpreadsheet method and you're right, it looks best for what I want to do. I would appreciate a bit of clarrification if you had some on how to perform it though. I imagine 1 making the ADO connection to the database, then creating a recordset to connect to the table. So I have ADODC Connection then ADODCrecordset opening my table. I found this code example:

    expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

    How do I clarrify and Identify that the TableName is the table I am opening in the recordset. The Example MSDN gives me is using the DoCmd portion. Can I use something similiar to.

    ADODC.TransferSpreadsheet (....) Where ADODC is my ADO Database Connection?

    Also what component must I activate to be able, if any, to use TransferSpreadsheet as a command. I notice it does not turn blue, or correct its Capitolization or utilize the code assistance when I begin to type it out as other commands do. I apologise if this is a bit lamen and noob of questions, but I am still trying to get the hang of Visual Basic, Thank you.

    the Darthmoob
  • dokken2dokken2 Posts: 532Member
    : : : Hello everyone, I have been reading up on importing cells and data from an Excel spreadsheet into an Access Database. Every Cell will pretty much be a field in the database. It is a small program for a friend of mine to take the data he had in an excel spread sheet, so he can querry and run reports from. There have been so many methods I have read to do this, but none were clear enough to make me think it was the fastest since it will be cell to field importing. I have coded importing from text files, using the Mid(STRING) and Left(STRING) methods, but I figured with excel it would be much different. Can someone please toss me a link, or maybe some example code for quick importing a spread sheet and seperating cell into field. Example:
    : : :
    : : : Cell A1 = First Name | Field FirstName
    : : : Cell A2 = Last Name | Field LastName
    : : :
    : : : Something like that.
    : : :
    : : : Thanks alot
    : : : The darthmoob
    : : :
    : :
    : :
    : : You can directly import the worksheet from the file/get external data menu. To programmatically import, see online help for the TransferSpreadsheet/TransferDatabase methods.
    : :
    : :
    :
    : Thanks Dokken, I looked into the TransferSpreadsheet method and you're right, it looks best for what I want to do. I would appreciate a bit of clarrification if you had some on how to perform it though. I imagine 1 making the ADO connection to the database, then creating a recordset to connect to the table. So I have ADODC Connection then ADODCrecordset opening my table. I found this code example:
    :
    : expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
    :
    : How do I clarrify and Identify that the TableName is the table I am opening in the recordset. The Example MSDN gives me is using the DoCmd portion. Can I use something similiar to.
    :
    : ADODC.TransferSpreadsheet (....) Where ADODC is my ADO Database Connection?
    :
    : Also what component must I activate to be able, if any, to use TransferSpreadsheet as a command. I notice it does not turn blue, or correct its Capitolization or utilize the code assistance when I begin to type it out as other commands do. I apologise if this is a bit lamen and noob of questions, but I am still trying to get the hang of Visual Basic, Thank you.
    :
    : the Darthmoob
    :

    The online help indicates that it can be used with ADO, however it is an intrinsic keyword and does not need any component/reference set.

    Syntax:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "NewTableName", "path-to-excel", HasFieldNames, Range

    Ex:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", "C:ook1.xls", True, "A1:B5"

  • darthmoobeydarthmoobey Posts: 140Member
    : : : : Hello everyone, I have been reading up on importing cells and data from an Excel spreadsheet into an Access Database. Every Cell will pretty much be a field in the database. It is a small program for a friend of mine to take the data he had in an excel spread sheet, so he can querry and run reports from. There have been so many methods I have read to do this, but none were clear enough to make me think it was the fastest since it will be cell to field importing. I have coded importing from text files, using the Mid(STRING) and Left(STRING) methods, but I figured with excel it would be much different. Can someone please toss me a link, or maybe some example code for quick importing a spread sheet and seperating cell into field. Example:
    : : : :
    : : : : Cell A1 = First Name | Field FirstName
    : : : : Cell A2 = Last Name | Field LastName
    : : : :
    : : : : Something like that.
    : : : :
    : : : : Thanks alot
    : : : : The darthmoob
    : : : :
    : : :
    : : :
    : : : You can directly import the worksheet from the file/get external data menu. To programmatically import, see online help for the TransferSpreadsheet/TransferDatabase methods.
    : : :
    : : :
    : :
    : : Thanks Dokken, I looked into the TransferSpreadsheet method and you're right, it looks best for what I want to do. I would appreciate a bit of clarrification if you had some on how to perform it though. I imagine 1 making the ADO connection to the database, then creating a recordset to connect to the table. So I have ADODC Connection then ADODCrecordset opening my table. I found this code example:
    : :
    : : expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
    : :
    : : How do I clarrify and Identify that the TableName is the table I am opening in the recordset. The Example MSDN gives me is using the DoCmd portion. Can I use something similiar to.
    : :
    : : ADODC.TransferSpreadsheet (....) Where ADODC is my ADO Database Connection?
    : :
    : : Also what component must I activate to be able, if any, to use TransferSpreadsheet as a command. I notice it does not turn blue, or correct its Capitolization or utilize the code assistance when I begin to type it out as other commands do. I apologise if this is a bit lamen and noob of questions, but I am still trying to get the hang of Visual Basic, Thank you.
    : :
    : : the Darthmoob
    : :
    :
    : The online help indicates that it can be used with ADO, however it is an intrinsic keyword and does not need any component/reference set.
    :
    : Syntax:
    : DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "NewTableName", "path-to-excel", HasFieldNames, Range
    :
    : Ex:
    : DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", "C:ook1.xls", True, "A1:B5"
    :
    :

    I see, so there would be no reference or components needed to use the DoCmd and execution, excellent. I have tried setting up my DB and the command through a button click function to test, and I keep getting an error.

    [code]
    Private Sub Command1_Click()
    Dim CentexDB As New ADODB.Connection

    CentexDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;persist security info =false;data source=c:CentexCentex.mdb"

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:CentexTEST.xls", True

    End Sub
    [/code]

    Do I need to create a Recordset Connection, and create the table already in the Database, before transfering spreadsheet. Or will the Spreadsheet Transfer "Create" a table with the table name I give?
    The Error Code is:

    Runtime Error '424':

    Object Required

    The error seems to point to my DoCmd Statement. Thank you for your Assistance in all this Dokken, I hope to have this finished ASAP, this is my first attempt in importing Excel into an Access DB using VB.

    The Darthmoob
  • dokken2dokken2 Posts: 532Member
    : Do I need to create a Recordset Connection, and create the table already in the Database, before transfering spreadsheet. Or will the Spreadsheet Transfer "Create" a table with the table name I give?
    : The Error Code is:
    :
    : Runtime Error '424':
    :
    : Object Required
    :
    : The error seems to point to my DoCmd Statement. Thank you for your Assistance in all this Dokken, I hope to have this finished ASAP, this is my first attempt in importing Excel into an Access DB using VB.
    :
    : The Darthmoob
    :


    The method does not require a DAO/ADO recordset and will create the table of your name from the excel data.

    Two omissions, you need a reference to the "Microsoft access 10.0 object library" and need to 'automate' access, that is programmatically open/control access for the TransferSpreadsheet method to work on an open instance of access [since there is no parameter for a path to the access db].


    Ex:
    [code]

    Dim objAccAppl As Access.Application

    'automate access
    Private Sub Command1_Click()
    'open my-db
    Set objAccAppl = GetObject("e:db1.mdb")
    'show access
    objAccAppl.Visible = True
    'hide access
    'objAccAppl.Visible = False
    End Sub


    'import excel
    Private Sub Command2_Click()
    On Error GoTo erh:

    'delete existing table
    DoCmd.DeleteObject acTable, "MyTable"
    xfer:
    'transfer excel
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyTable", "e:ook1.xls", True, "A1:B5"

    xit:
    Exit Sub
    erh:
    If Err.Number = 7874 Then Resume xfer 'table does not exist
    MsgBox Err.Description, vbCritical, Err.Number
    Resume xit
    End Sub


    'quit access
    Private Sub Command3_Click()
    objAccAppl.Quit acQuitSaveNone
    Set objAccAppl = Nothing
    End Sub

    [/code]

Sign In or Register to comment.