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