Importing data from Excel 2003 to Access 2003

[Eric]

I am very experienced with Access and databases in general. I am also very
experienced with Excel and VBA for Excel. I have never written code directly
in Access.

I have code written in Excel that does basic data cleansing and prep. What I
need is to be able to run that code from within Access so that the data will
be arranged in Excel and then imported to the proper tables in Access so that
the import procedure doesn't have to be done manually.

I would appreciate it if someone would show me just a basic sample of code
that takes the contents (make it simple, just one piece of data - I can fill
in the rest) of an Excel file and puts the data in an Access table. Also,
where would you put the code in Access? Do you create a module and then make
a macro that executes the module. Oh yeah, and it would be great if all the
code to do the Excel work could actually be held in the Access file so I
don't have to run Excel code and then separately run the Access code.

Any help would be greatly appreciated.

Thanks - Eric

 

[Piet Linden]

I have code written in Excel that does basic data cleansing and prep. What I
need is to be able to run that code from within Access so that the data will
be arranged in Excel and then imported to the proper tables in Access so that
the import procedure doesn't have to be done manually.

I would appreciate it if someone would show me just a basic sample of code
that takes the contents (make it simple, just one piece of data - I can fill
in the rest) of an Excel file and puts the data in an Access table. Also,
where would you put the code in Access? Do you create a module and then make
a macro that executes the module. Oh yeah, and it would be great if all the
code to do the Excel work could actually be held in the Access file so I
don't have to run Excel code and then separately run the Access code.

Any help would be greatly appreciated.

Thanks - Eric

Eric,
Why not just link to your Excel file and use a Range (if you have to)
in order to import the data in one fell swoop. Much easier that
way... Ken Snell has tons of examples on his website.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Lots of examples to get you started...

 

[Eric]

Thanks for the help.

The following code (that I got from a website) works fine when I create it
as a separate module and run it from the VB debugging window. It goes to the
2 files in the folder I specified and imports the data from the files.
Ultimately, though, I want to create a button on a form to run this code. I
tried attaching the same code to the On Click event for a button as an Event
Procedure. When I click the button I get an error saying Object or class does
not support the set of events. Is there something special I have to do to
this code to get it to run from a button?

A related question: WHen I create the code below as a module in Access, I
see the name of the module in the module list, but the Run button is greyed
out. For some reason the only way I can run it is by opening it in the VB
window and running it through the debugger. Why is this?

Thanks for any help. Here is the code:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\testdb\"

' Replace tablename with the real name of the table into which
' the data are to be imported
strTable = "tblnames"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

 

[Ken Snell [MVP]]

On which code step does the debugger highlight for the "object/class" error?

Post the entire code of the button's click event procedure (including
Private Sub and End Sub lines).

 

[Eric]

OK, I think I found the problem. I have both Access 2003 and 2007 installed
on the same computer. I'm trying to do this work in 2003. While I was
troubleshooting I found that even if I commented out all of the code, I still
got the same error. So I tried making a button with the wizard that would
open Excel and I still got the same error. I tried redoing all of this in
Access 2007 and it all worked fine. So it seems that 2007 was interfering
with the functionality of 2003. I'm just doing it all in 2007 and saving in
the 2003 format.

Thanks for the help guys!

-Eric