Exporting Excel spreadsheets to MS Access thro code


Posted by Aps on October 08, 2001 8:02 AM

Could some1 send me the required code to export excel spreadsheets to Access?

Thanks

Posted by GREGC on October 08, 2001 11:27 AM

Do you want the data from the spreadsheet. I have a timesheet that feeds an employees times into an excel data base that we use for job costing. It uses DAO code to put times and job #s into a database.

Posted by Aps on October 09, 2001 2:58 AM

Yes. I need the DAO code for posting data from the Spreadsheet into a database table.

Thanks in advance...

Posted by gregc on October 09, 2001 8:11 AM

DAO code to update database

Sub Update_Database()
On Error GoTo error_update_database

'

' Update_Database Macro
' Macro recorded 12/18/2000 by GREG
'


Dim dbs As DAO.Database
Dim rstTimeSheet As DAO.Recordset
Dim rstTimeSheetDetail As DAO.Recordset
Dim wks As DAO.Workspace

If Range("B51") = 1 Then
MsgBox "This sheet has already been entered into the database" & vbNewLine & "on " & Range("b52"), vbOKOnly, "Database update"
Exit Sub
End If

strCode = InputBox("Enter the password to update the database.", "Password")

If strCode <> Range("B50") Then
MsgBox "The password you entered is incorrect", , "Password Error"
Exit Sub
End If


'Update Database

Application.ScreenUpdating = False

strDatabase = InputBox("Enter the database to import to.", "Database", "U:\Greg\Job Costing .MDB")

Set wks = DBEngine.Workspaces(0)


Set dbs = OpenDatabase(strDatabase)
Set rstTimeSheet = dbs.OpenRecordset("tblTimeSheet", dbOpenDynaset)
Set rstTimeSheetDetail = dbs.OpenRecordset("tblTimeSheetDetail", dbOpenDynaset)

' Get info from the speadsheet
strEmployeeNumber = Range("K3")
dtmStartDate = Range("D39")
strDepartment = Range("K8")



Range("I9").Select

wks.BeginTrans

rstTimeSheet.AddNew

rstTimeSheet!datWeekNumber = dtmStartDate
rstTimeSheet!intEmployeeNumber = strEmployeeNumber

rstTimeSheet.Update

While intCounter < 23
ActiveCell.Offset(1, 0).Select
If ActiveCell > 0 Then
dblHours = ActiveCell
ActiveCell.Offset(0, 1).Select
strJobNumber = ActiveCell
ActiveCell.Offset(0, 1).Select
strDescription = ActiveCell


rstTimeSheetDetail.AddNew

rstTimeSheetDetail!intWeekNumber = dtmStartDate
rstTimeSheetDetail!intEmployeeNumber = strEmployeeNumber
rstTimeSheetDetail!strJobNumber = strJobNumber
rstTimeSheetDetail!dblRegularHours = dblHours
rstTimeSheetDetail!strDepartmentAbreviation = strDepartment
rstTimeSheetDetail!strDescription = strDescription

rstTimeSheetDetail.Update

ActiveCell.Offset(0, -2).Select
End If

intCounter = intCounter + 1
dblTotalHours = Val(dblHours) + dblTotalHours

' Clear variables
dblHours = ""
strJobNumber = ""
strDescription = ""

Wend




Range("b51") = 1
Range("b52") = Now

wks.CommitTrans

ActiveWorkbook.Save
MsgBox "A total of " & dblTotalHours & " hours were imported" & vbNewLine & "into the Job Costing database for " & Range("k5"), , "Import Complete"

rstTimeSheet.Close
rstTimeSheetDetail.Close
dbs.Close

Exit Sub

error_update_database:

intErrorNum = Err.Number
If Err.Number = 3201 Then
MsgBox "An error has occured for the following reason." & vbNewLine & Err.Description, , "Error"
Else
MsgBox "An error has occured" & vbNewLine & Err.Number & " , " & Err.Description, , "Error"
End If

wks.Rollback
'
End Sub



Posted by Gregc on October 09, 2001 8:16 AM

Code.

If you have a book on Excel or Access it helps to explain the database updated code. It is fairly easy once you get the concept.