Here is my code.
I pasted my code in a .txt file so it can be easily read.
Thanks!!
Expand|Select|Wrap|Line Numbers
- Private Sub Command35_Click()
- On Error GoTo Err_Command0_Click
- Dim appExcel As Object
- Dim workBook As Object
- Dim workSheet As Object
- Dim i As Integer
- Dim FdrID As Integer
- Dim dbs_curr As Database
- Dim records As Recordset
- Dim sqlStatement As String
- Dim message As String
- Set dbs_curr = CurrentDb
- ' Open an existing spreadsheet
- Set appExcel = GetObject("S:\Workgroups\file1.xls (This one works)
- GetObject ("S:\Workgroups\file2.xls (This one doesnt work currently)
- GetObject ("S:\Workgroups\File3.xls (This one doesnt work currently)
- ' Don't show spreadsheet on screen
- appExcel.Application.Visible = False
- Set workSheet = appExcel.Worksheets(1)
- i = 2
- While workSheet.Cells.Range("A" & i & ":A" & i).Value <> ""
- sqlStatement = "SELECT GetData.* FROM GetData WHERE PDArea IS NULL"
- Set records = dbs_curr.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
- records.AddNew
- records!PDArea = workSheet.Cells.Range("A" & i & ":A" & i).Value
- records!Company = workSheet.Cells.Range("B" & i & ":B" & i).Value
- records!Supervisor = workSheet.Cells.Range("C" & i & ":C" & i).Value
- records!GF = workSheet.Cells.Range("D" & i & ":D" & i).Value
- records!Foreman = workSheet.Cells.Range("E" & i & ":E" & i).Value
- records!current = workSheet.Cells.Range("F" & i & ":F" & i).Value
- records!ReportedIllnesses = workSheet.Cells.Range("G" & i & ":G" & i).Value
- records!Sick = workSheet.Cells.Range("H" & i & ":H" & i).Value
- records!Date = workSheet.Cells.Range("I" & i & ":I" & i).Value
- records.Update
- i = i + 1
- Wend
- ' Release objects
- Set workSheet = Nothing
- Set workBook = Nothing
- Set appExcel = Nothing
- MsgBox "Done"
- Exit_Command0_Click:
- Exit Sub
- Err_Command0_Click:
- MsgBox Err.Description
- Resume Exit_Command0_Click
- End Sub