›

Importing Excel to Access

darthmoobeydarthmoobey
in Visual Basic
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
    : 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
    : : 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
    : : : 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
    : : : : 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
    : 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.

Howdy, Stranger!

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

Sign In Register

Categories

In this Discussion