Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MICROSOFT ACCESS 2007 - importing excel files into a table.

Posted on 2011-02-15
7
Medium Priority
?
349 Views
Last Modified: 2012-05-11
I have the following code to import excel files into access table.  But it only inserts the first row of each file.  I need it to insert all the rows of each column.


Public Function MYPREMLOAD()

Dim strPath As String
Dim appExcel As Excel.Application
Dim strFolderPath As String
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
  
DoCmd.SetWarnings False
  strSQL = "Delete * From tblCustomer;"
  DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
  
Set MyDB = CurrentDb()

Set MyRS = MyDB.OpenRecordset("tblCustomer", dbOpenDynaset)
  
'strFolderPath = "C:\Customers\"
'strPath = "C:\Customers\*.xls"     'Set the path.
  
strFolderPath = "X:\Special Risk\miketesting\POI\"
strPath = "X:\Special Risk\miketesting\POI\*.xls"
  
strPath = Dir(strPath, vbNormal)   'Retrieve the first entry.
Set appExcel = CreateObject("Excel.Application")
  
Do While strPath <> ""    'Initiate the loop
  appExcel.Workbooks.Open strFolderPath & strPath
  appExcel.Visible = True
  appExcel.Sheets("SR").Select
    With MyRS
      .AddNew
        !Policy = appExcel.Range("D2").Value
        !Effective = appExcel.Range("F2").Value
        !Yr = appExcel.Range("G2").Value
        !Premium = appExcel.Range("J2").Value
      .Update
    End With
  strPath = Dir           'Next entry
Loop
  
appExcel.Quit
Set appExcel = Nothing
  
MyRS.Close
Set MyRS = Nothing
  
MsgBox "This Process has completed!"
  

End Function

Open in new window

0
Comment
Question by:centralmike
[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
  • 4
  • 2
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34905317
Try this

Const xlUp = -4162

Public Function MYPREMLOAD()
    Dim strPath As String, strFolderPath As String, strFolderPath As String
    Dim appExcel As Excel.Application
    Dim MyDB As DAO.Database, MyRS As DAO.Recordset
    Dim LastRow As Long
    
    DoCmd.SetWarnings False
    strSQL = "Delete * From tblCustomer;"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
 
    Set MyDB = CurrentDb()
    
    Set MyRS = MyDB.OpenRecordset("tblCustomer", dbOpenDynaset)
     
    strFolderPath = "X:\Special Risk\miketesting\POI\"
    strPath = "X:\Special Risk\miketesting\POI\*.xls"
     
    strPath = Dir(strPath, vbNormal)
    Set appExcel = CreateObject("Excel.Application")
     
    Do While strPath <> ""
        appExcel.Workbooks.Open strFolderPath & strPath
        appExcel.Visible = True
        With MyRS
            LastRow = Sheets("SR").UsedRange.SpecialCells(xlCellTypeLastCell).Row
            
            For i = 2 To LastRow
                .AddNew
                !Policy = appExcel.Range("D" & i).Value
                !Effective = appExcel.Range("F" & i).Value
                !Yr = appExcel.Range("G" & i).Value
                !Premium = appExcel.Range("J" & i).Value
                .Update
            Next
            
        End With
        strPath = Dir
    Loop
     
    appExcel.Quit
    Set appExcel = Nothing
     
    MyRS.Close
    Set MyRS = Nothing
     
    MsgBox "This Process has completed!"
End Function

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34905325
Replace line 1

Const xlUp = -4162

by

Const xlCellTypeLastCell = 11

Sid
0
 

Author Comment

by:centralmike
ID: 34906695
Thanks the code works great.  Just one addition question.  What if I want to start reading the files at line 3 of all the worksheets.  Is there away do accomplish that?

Thanks

Mike
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 34906730
Change

For i = 2 To LastRow

to

For i = 3 To LastRow

Sid
0
 

Author Comment

by:centralmike
ID: 34907388
Can column names be used instead of column letters?  What if the policy is column "M" instead of column "D".  Can you reference the name of column in stead of the letter?

Thanks again Mike
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34910056
Yes

Col A - 1
Col B - 2

and so on...

So if you want to refer to Range("A1") then you can also say Cells(1,1) or if you want to refer to range("A2") the you can also refer to it as cells(2,1) where the syntax is

Cells(Row,Column)

Sid
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

I recently attended Cisco Live! in Las Vegas, a conference that boasted over 28,000 techies in attendance, and a week of hands-on learning hosted by a solid partner with which Concerto goes to market.  Every year, Cisco displays cutting-edge technol…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

670 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