Solved

I need help importing data from an Excel file into Access

Posted on 2014-12-18
10
558 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
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.

 

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 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 35

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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