Hi,
Hoping someone may be able to give a bit of guidance on how I can achieve something.
I'm current working on creating a database that allows my company to track completion of online training of various different training modules.
The information all comes in as multiple excel files and has 20+ columns (of which I only need 2/3) which I then want to pull together into a single table within access.
The easiest way I found to do this was to import the entire excel sheet and then use an append query to add the columns I need from there. I've achieved that using the code below. However, the problem comes when some of the excel files have multiple training modules on them so I need to cycle through these and them as well.
Basically it comes like this
[EmployeeID] [MOD 1 STATUS] [MOD 2 STATUS] etc etc
010101 Successful Not Regsitered
What I want to do is to be able to take that and put it in to a table whereby the module is given an ID and it becomes this
[ModuleID] [FileID] [Status]
1 010101 Successful
2 010101 Not Registered
Any ideas would be massively appreciated.
Hoping someone may be able to give a bit of guidance on how I can achieve something.
I'm current working on creating a database that allows my company to track completion of online training of various different training modules.
The information all comes in as multiple excel files and has 20+ columns (of which I only need 2/3) which I then want to pull together into a single table within access.
The easiest way I found to do this was to import the entire excel sheet and then use an append query to add the columns I need from there. I've achieved that using the code below. However, the problem comes when some of the excel files have multiple training modules on them so I need to cycle through these and them as well.
Basically it comes like this
[EmployeeID] [MOD 1 STATUS] [MOD 2 STATUS] etc etc
010101 Successful Not Regsitered
What I want to do is to be able to take that and put it in to a table whereby the module is given an ID and it becomes this
[ModuleID] [FileID] [Status]
1 010101 Successful
2 010101 Not Registered
Any ideas would be massively appreciated.
Code:
Set Db = CurrentDb
Set appExcel = CreateObject("Excel.Application")
DoCmd.RunSQL "DELETE * FROM tbl_TrainingData"
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
If FilePicker.Show = True Then
For Each VarItem In FilePicker.SelectedItems
StrModuleData = Dir(VarItem)
StrFPath = VarItem
Next
End If
Set MyWorkbook = appExcel.Workbooks.Open(StrFPath)
appExcel.Visible = False
With MyWorkbook
.Sheets("Sheet1").Rows("1:9").EntireRow.Delete
.Sheets("Sheet1").Range("C1").Value = "FileID"
ModuleNameExcel = .Sheets("Sheet1").Range("T1").Value
On Error GoTo Err_NoModuleID
ModuleCode = DLookup("[ModuleID]", "tbl_Modules", "[ModuleName] = '" & ModuleNameExcel & "'")
On Error Resume Next
.Sheets("Sheet1").Range("T1").Value = "Status"
.Save
.Close
End With
DoCmd.TransferSpreadsheet acImport, 8, "tbl_ImportedExcel", StrModuleData, True, ""
StrInsert = "INSERT into tbl_TrainingData(FileID,Status) "
StrSelect = "SELECT FileID, Status1 FROM tbl_ImportedExcel"
StrSQL = StrInsert & StrSelect
DoCmd.RunSQL StrSQL
StrUpdate = "UPDATE tbl_TrainingData SET tbl_TrainingData.ModuleID = " & ModuleCode & _
" WHERE (((tbl_TrainingData.ModuleID) Is Null))"
DoCmd.RunSQL StrUpdate
Set FilePicker = Nothing
Set Db = Nothing
Exit Sub
Err_NoModuleID:
MsgBox "There is no corresponding module for this data in tbl_Modules. Please add the module information to tbl_Modules and try again", vbOKOnly, "Missing information"
Exit Sub
End Sub