Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.