?
Solved

I need help importing data from an Excel file into Access

Posted on 2014-12-18
10
Medium Priority
?
671 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 38

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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