?
Solved

I need help importing data from an Excel file into Access

Posted on 2014-12-18
10
Medium Priority
?
759 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
9 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40507495
So, what is getting imported in that column, anything?
0
 
LVL 49

Expert Comment

by:Dale Fye
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40507953
mrotor,

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

Accepted Solution

by:
PatHartman earned 2000 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 50

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

850 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