[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

I need help importing data from an Excel file into Access

Posted on 2014-12-18
10
Medium Priority
?
724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 48

Expert Comment

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

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 48

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 39

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 49

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

649 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