PDA

View Full Version : Macro that automatically imports excel data into access database?



truzilla
07-08-2008, 08:06 AM
Hey everyone! Hope you all had a great July 4th weekend. So i'm new with working with access and I truly appreciate any help given. Here is my situation and problem:

I have an excel spread sheet that contains a macro which is already complete. I need to add some VB code to the macro that will
1) run the macro
2) open up a specific access database, with a specific table
3) import 1 specific spreadsheet of the workbook (after running the completed macro) into the database, replacing any old data that was previously in the access data table

I understand that there are no specific names but im sure you can just make up the names or just use these for reference.

1) Access database: testdatabase
2) Excel book: ExcelTest
3) Spreadsheet name with macro: Revenues
4) Macro name: Macro1

THANKS again for all your help and let me know if I need to clarify anything! :bow:

mdmackillop
07-08-2008, 09:13 AM
Here's a version of one that I use

Sub Exports(LRw As Long)
'Requires reference to Microsoft DAO 3.6 Object Library
Dim Cel As Range
Dim MyDate As Date
Dim Employee As Long
Dim JobNo As Long
Dim Category As String
Dim Hours As Double

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Establish connection
Set dbs = OpenDatabase("S:\QS\Timesheets\Time.mdb")
'Fill recordset
Set rst = dbs.OpenRecordset("Hours")
With rst
' add values to each field in the record
For Each Cel In Sheets("Summary").Range("B5:L" & [B65536].End(xlUp).Row)
If Cel > 0 Then
.AddNew ' create a new record
JobNo = Sheets("Summary").Cells(Cel.Row, 1)
Category = Sheets("Summary").Cells(4, Cel.Column)
Hours = Round(Cel, 2)
.Fields("Date") = MyDate
.Fields("EmployeeNo") = Employee
.Fields("JobNo") = JobNo
.Fields("Category") = Category
.Fields("Hours") = Hours
.Update ' stores the new record
End If
Next
End With
skipped:
Exit Sub
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

truzilla
07-08-2008, 11:47 AM
thanks for the quick response. I was just a little confused about the naming conventions. For example this section:

.AddNew ' create a new record
Date = Sheets("Formatting").Cells(Cel.Row, 1)
Region = Sheets("Formatting").Cells(4, Cel.Column)
Hours = Round(Cel, 2)
.Fields("Date") = MyDate
.Fields("EmployeeNo") = Employee
.Fields("JobNo") = JobNo
.Fields("Category") = Category
.Fields("Hours") = Hours
.Update ' stores the new record

what do the bolded words tell me and how can I use them to adjust it to my own spread sheet. Thanks!!

truzilla
07-08-2008, 12:34 PM
OK, so I don't care about that too much - I can figure it out by trial and error. Right now I can't get the macro to run because of the names of the databases? Do I need to change the names of the DAO portions? These lines:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

How do I need to adjust it to work with my excel spreadsheet and database? Thanks!!

truzilla
07-08-2008, 02:22 PM
so i fixed my previous error by referencing the correct DAO 3.6. Object Library BUT, below is my code and it keeps getting jammed up at the first adding of data (from Date1). whats my problem?

Sub Exports()
'Requires reference to Microsoft DAO 3.6 Object Library

Dim LRw As Long
Dim Cel As Range
Dim Date1 As Date
Dim Revenue As Long
Dim Region As String
Dim Level As String


Dim dbs As DAO.Database
Dim rst As DAO.Recordset


'Establish connection
Set dbs = OpenDatabase("C:\database")
'Fill recordset
Set rst = dbs.OpenRecordset("ABC")
With rst
' add values to each field in the record
For Each Cel In Sheets("ABC").Range("A1:D" & [B65536].End(xlUp).Row)
If Cel > 0 Then
.AddNew ' create a new record
Date1 = Sheets("ABC").Cells(1, Cel.Column)
Region = Sheets("ABC").Cells(2, Cel.Column)
Level = Sheets("ABC").Cells(3, Cel.Column)
Revenue = Sheets("ABC").Cells(4, Cel.Column)
.Fields("Date") = Date1
.Fields("Region") = Region
.Fields("HFM LVL 4") = Level
.Fields("Revenue $MM") = Revenue
.Update ' stores the new record
End If
Next
End With
skipped:
Exit Sub
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

Basically my data has dates in Column A, Regions in B, Titles in C, and Revenues in D - ALL the way down to row 16,000.