Solved

I need help importing data from an Excel file into Access

Posted on 2014-12-18
10
522 Views
Last Modified: 2015-01-24
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:
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

Open in new window


Excel Data Sample:
Excel data sample

mrotor
0
Comment
Question by:mainrotor
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40507495
So, what is getting imported in that column, anything?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40507496
Have you tried linking that table into Access?  If so, what is visible when you do that?
0
 

Author Comment

by:mainrotor
ID: 40507913
Dale,
There's nothing being imported into that column.  That's the problem.  

mrotor
0
 

Author Comment

by:mainrotor
ID: 40507918
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
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40507953
mrotor,

Does that last comment refer to when the worksheet is linked to access, or imported?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40508158
The combo box is a separate table somewhere else on the sheet.  You have several options:
1. Import the lookup table the combo is pointing to into Access as a separate table.
2. Create a new table in Access and type in the values
3.  Modify the table in design view and add a lookup for the field where you type the values into a value list.

To use the lookup table created by 1 or 2 or 3, add a combo box to the form that points to the table created in 1 or 2.  If you embedded a value list (worst choice), the value list will be copied to the form but value lists created this way are difficult to manage because if you have to change them, you have to change them in multiple places although if you always only change the table, you should get an option to propagate the change depending on what version of Access you are using.
0
 

Author Comment

by:mainrotor
ID: 40520063
Dale,
My comment referred to when the worksheet is imported?

mrotor
0
 

Author Comment

by:mainrotor
ID: 40520064
Pat,
I will try your suggestions and post my outcome.

mrotor
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40567980
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 80
Library not Registered 16 45
Search Form with Run Time Error 3075 1 22
Set focus on next field when character count = 5 9 12
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now