Excel 2007 to Access 2007

[Lorlai]

Hello,

I'm new to the VBA scene, and am trying to do some research on different methods. I have an Excel spreadsheet that is generated by some software. I would like to get this data into an Access database without having to go through the steps of importing the data through Access. Is this possible to do? From my research, I have found it is possible to create a connection to a database and can perform queries, but would like to have the data physically imported into the database.

Alternativly, if it isn't possible to export the data without going directly into Access and pressing the import from excel button, would it be possible to automate the process that the import wizard takes you through?

Thanks for the help!

 

[Trevor G]

Welcome to the forum,

Here is some code to get you going, please note some comments inside the code

Sub ExcelToAccess()
'You must set the reference to Access
'Tools Menu, References and then look for Microsoft Access, tick the box
' exports data from the active worksheet
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb") 'Change Path and Database name
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable) 'Add your table name
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value 'Change to Field name
.Fields("FieldName2") = Range("B" & r).Value 'Change to Field name
.Fields("FieldNameN") = Range("C" & r).Value 'Change to Field name
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

 

[Lorlai]

Hi Trevor,

Thank you for your welcome and for your fast reply! I have seen this code before and had tried to implement it, and ran into the error on the

Set db = OpenDatabase("C:\Users\user\Documents\Test.mdb")

(Where user is the appropriate name). VBA doesn't seem to like the .mdb extension, and tells me that it can't find the file. When I enter the same extension as the the database (.accdb), I recieve the runtime error 3343, where the format is unrecognizable. I did some searching, and can't find an answer to this error. Any further advice on this?

Thank you!

 

[Trevor G]

Without seeing the database and spreadsheet it becomes a little difficult to give exact instructions. However what about this as a work around !

Can you edit the database so you can add a MACRO which will allow you to TransferSpreadsheet (in 2007 when you create a macro you don't see all the commands by default so you have to select Show All Actions then the option will appear) this will transfer the spreadsheet and if successful you don't need VBA code.

 

[Lorlai]

Hi Trevor,

Thanks again for your response. I think I am going to go exactly this route. You have been a great help; thank you so much!

 

[Trevor G]

I hope it works in the way you want, if not post back.