mainrotor
asked on
I need help importing data from an Excel file into Access
Hi Experts,
I need help importing data from an Excel file into Access
In my Excel file I have 3 columns with data. One of the columns is made up of combo boxes that the users select a value from.
When I import the file into Access, using the code below, all of the data is imported successfully except for the values in the Combo Boxes.
How can I get those values imported into my Access database? Thank you very much in advance.
My Code:
Excel Data Sample:
mrotor
I need help importing data from an Excel file into Access
In my Excel file I have 3 columns with data. One of the columns is made up of combo boxes that the users select a value from.
When I import the file into Access, using the code below, all of the data is imported successfully except for the values in the Combo Boxes.
How can I get those values imported into my Access database? Thank you very much in advance.
My Code:
Private Sub Command2_Click()
Const cstrFolder As String = "C:\Schedules\"
Dim i As Long, x As Long, lng As Long
Dim xlApp As Object
Dim xlWrk As Object
Dim xlSheet As Object
Dim sql As String
Dim strExt As String, strFile As String, strTable As String
Set xlApp = VBA.CreateObject("Excel.Application")
xlApp.Visible = False
'
strExt = ".xls"
lng = Len(strExt)
strFile = Dir(cstrFolder & "*" & strExt)
If Len(strFile) = 0 Then
MsgBox "No Files Found"
Else
Do While Len(strFile) > 0
'MsgBox (cstrFolder & " - " & strFile)
'ADD EXCEL CODE HERE
Set xlWrk = xlApp.Workbooks.Open(cstrFolder & strFile) '("C:\ExcelImportFile.xls")
Set xlSheet = xlWrk.Sheets("Sheet1")
For i = 11 To 41
sql = "Insert Into [tblTechAvailability] (Day,Availability,Notes) VALUES ('" & xlSheet.Cells(i, 1).Value & "','" & xlSheet.Cells(i, 2).Value & "','" & xlSheet.Cells(i, 3).Value & "')"
DoCmd.RunSQL sql
Next i
xlWrk.Close
'xlApp.Quit
Set xlSheet = Nothing
Set xlWrk = Nothing
'Set xlApp = Nothing
'END EXCEL CODE HERE
x = x + 1 'KEEPS COUNT OF IMPORTED FILES
strFile = Dir()
Loop
xlApp.Quit
Set xlApp = Nothing
MsgBox x & " File(s) were imported"
End If
End Sub
Excel Data Sample:
mrotor
So, what is getting imported in that column, anything?
Have you tried linking that table into Access? If so, what is visible when you do that?
ASKER
Dale,
There's nothing being imported into that column. That's the problem.
mrotor
There's nothing being imported into that column. That's the problem.
mrotor
ASKER
Dale,
The data under the Day and Notes columns comes over just fine. But the values from the combo boxes don't import at all.
mrotor
The data under the Day and Notes columns comes over just fine. But the values from the combo boxes don't import at all.
mrotor
mrotor,
Does that last comment refer to when the worksheet is linked to access, or imported?
Does that last comment refer to when the worksheet is linked to access, or imported?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dale,
My comment referred to when the worksheet is imported?
mrotor
My comment referred to when the worksheet is imported?
mrotor
ASKER
Pat,
I will try your suggestions and post my outcome.
mrotor
I will try your suggestions and post my outcome.
mrotor
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.