Solved

Import all excel files in a folder into access

Posted on 2011-09-08
10
364 Views
Last Modified: 2012-05-12
I have an access database, and 200 excel files. I am trying to pull the data from all the workbooks in a folder. Not all the data on the sheet needs to be pulled, and I am only writing to one table.

I have not touched vba in over 10 years, please help.
Thanks
0
Comment
Question by:kmccollum
  • 6
  • 4
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36507585

this should do it
sub importXL()
dim xlFile as string, xlPath as string
xlPath="C:\Folder1\"

xlFile=dir(xlPath & *.xls")

while xlFile <>""

         docmd.transferspreadsheet acimport,, "NameOfTable", xlpath & xlFile, "NameOfSheet!", True

         xlFile=dir
Wend

end sub

Open in new window



post back if you need more info
0
 

Author Comment

by:kmccollum
ID: 36507644
not all of the data in the sheet is imported, i need to be able to pick out data.

cell (2,5)
cell(1,6)
 rows 7 through 200 cells (1,2,3,4,5 & 6)

then data needs to be written to the table with the top two cells attached to each row 7-200

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36507750
a sample excel file  would be needed.. upload some excel files.
0
Industry Leaders: 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:kmccollum
ID: 36507807
Here is a sample file

I need to pull the 2E as a variable (id)
Then start at row 7 and pull A,B,D,E,F (only pull were description not empty)

Insert data for each row into table
     Insert ProductInformation () Values (id,A,B,D,E,F)


ClothingSaleInventorySheetfall20.xls
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36507836
how about the table, can you upload a copy of the db with the table..
0
 

Author Comment

by:kmccollum
ID: 36507852
Database name:PHICA
Table name: ProductInformation

Columns:
   ID (auto increment int)
   ConsinerInformationID (string)
   ItemNumber (string)
   Description (string)
   Size (string)
   Price (currency)
   Half (yes/no)
   Sold (yes/no)
   SoldAmt (currency)
   EarnAmt (currency)

   
0
 

Author Comment

by:kmccollum
ID: 36507865
i have played with the code that you put out at first but i can't find out how to insert data into table


Dim filePath As String

filePath = "C:\Faith Clothing Sale\consignors 9-11"

Public Function ImportFilesInFolder()
    Dim xlFile As String, xlPath As String
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    
    Dim rowCount As Integer, r As Integer
    Dim conID As String, itemNumber As String, description As String
    Dim size As String, price As String, half As String, soldAmt As String
    
        
    Set xlApp = New Excel.Application
    xlPath = filePath
    
    xlFile = Dir(xlPath & "*.xls")

    While xlFile <> ""
            'DoCmd.TransferSpreadsheet acImport, , "NameOfTable", xlPath & xlFile, "NameOfSheet!", True
            
            xlApp.Visible = False
            Set xlWB = xlApp.workbooks.Open(xlFile, , False)
            Set xlWS = xlWB.Worksheets(1)
            
            conID = xlWS.Cells(2, 5).Value
            rowCount = xlWS.UsedRange.Rows.Count
            
            For r = 7 To rowCount
                'pull data
                itemNumber = xlWS.Cells(r, 1).Value
                description = xlWS.Cells(r, 2).Value
                size = xlWS.Cells(r, 4).Value
                price = xlWS.Cells(r, 5).Value
                half = xlWS.Cells(r, 6).Value
                soldAmt = xlWS.Cells(r, 8).Value
                
                If description <> "" Then
                    'insert data
                End If
            Next
            
             xlFile = Dir
    Wend

End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36507873
your excel columns does not match the fields of the table, check the excel file and mapped the columns or cell to fields in the table...
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 36507979
test this code



Public Function ImportFilesInFolder()
    Dim xlFile As String, xlPath As String
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    
    Dim rowCount As Integer, r As Integer
    Dim conID As String, itemNumber As String, itmDescription As String
    Dim size As String, price As Double, half As Integer, soldAmt As Double
    
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("ProductInformation")
'    Set xlApp = New Excel.Application
    xlPath = filePath
    
    xlFile = Dir(xlPath & "*.xls")

    While xlFile <> ""
            'DoCmd.TransferSpreadsheet acImport, , "NameOfTable", xlPath & xlFile, "NameOfSheet!", True
            Set xlApp = New Excel.Application

            xlApp.Visible = False
            Set xlWB = xlApp.workbooks.Open(xlPath & xlFile, , False)
            Set xlWS = xlWB.Worksheets(1)
            
            conID = xlWS.Cells(2, 5).Value
            rowCount = xlWS.UsedRange.Rows.Count
            
            For r = 7 To rowCount
                'pull data
                itemNumber = xlWS.Cells(r, 1).Value
                itmDescription = xlWS.Cells(r, 2).Value
                size = xlWS.Cells(r, 4).Value
                price = xlWS.Cells(r, 5).Value
                half = xlWS.Cells(r, 6).Value
                soldAmt = xlWS.Cells(r, 8).Value
                
                If itmDescription <> "" Then
                    'insert data
                    With rs
                        .AddNew
                        !ConsinerInformationID = conID
                        !itemNumber = itemNumber
                        !description = itmDescription
                        !size = size
                        !price = price
                        !half = IIf(half = "y", -1, 0)
                        !soldAmt = soldAmt
                        .Update
                    End With
                End If
            Next
            Set xlWS = Nothing
            Set xlWB = Nothing
            xlApp.Quit
            Set xlApp = Nothing
        xlFile = Dir
    Wend

End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36513308
is it working ?
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

733 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