Importing Excel to Access

darthmoobey

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

dokken2

: 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.

darthmoobey

: : 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

dokken2

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

darthmoobey

: : : : 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

dokken2

: 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]