Import multiple excel files with inconsistent number of worksheets in each into ACCESS 
Author Message
 Import multiple excel files with inconsistent number of worksheets in each into ACCESS

GREETINGS:

The following code is a function shared with me from an ACCESS developers
board.  For some reason it hangs up my computer and causes me to have to
re-start.  The objective is to import multiple Excels workbooks with an
inconsistent number of worksheets in each file.

Any assistance will be greatly appreciated.

Private Sub test_import_Click()

Dim rs As New DAO.Recordset
Dim xDir As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim iSheet As Integer, iRow As Integer, iCol As Integer

Const MyPath = "C:\MyDocuments\"

Set xlApp = New Excel.Application

xDir = Dir(MyPath & "*.xls")

    Do Until xDir = ""
        Set xlBook = xlApp.Workbooks.Open(MyPath & xDir)
        iSheet = 1 'this is the looping variable for the sheets in the file
xDir
            Do Until iSheet > xlBook.Sheets.Count
                With xlBook.Sheets(iSheet)
                    .Activate 'Activates the current sheet of the workbook
                    iRow = 2  'This allows for row 1 to be a header row
                    Do Until IsEmpty(.Cells(iRow, 1)) 'Append will stop when
an empty cell is encountered in column A'
                        rs.AddNew 'Puts a new empty recrod in the access
table
                        For iCol = 1 To rs.Fields.Count - 1 'Prepare to read
the current row of the Excel sheet
                            rs(iCol) = .Cells(iRow, iCol)
                        Next iCol
                        rs.Update
                        iRow = iRow + 1
                    Loop
                End With
                iSheet = iSheet + 1
            Loop
        xlBook.Close
        xDir = Dir
    Loop

xlApp.Quit 'Exits Excel

'Good Housekeeping
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End Sub

--
Lydia M. Frank



Sat, 09 Nov 2002 03:00:00 GMT  
 Import multiple excel files with inconsistent number of worksheets in each into ACCESS
Lydia,
From what I can see;
I assume you have a reference to Excel set. If you don't ...
Dim xlApp As Excel.Application
...will cause an error.

You are not setting rs to anything.
Although you Dim rs as a New DAO.Recordset, you do not then set it to
actually open a recordset so that you can add your records. See
OpenRecordset in the help.

What is cnn?
cnn.Close
Set cnn = Nothing
Take them out.
--
Hope This Helps,
Jeff Davies


Quote:
> GREETINGS:

> The following code is a function shared with me from an ACCESS developers
> board.  For some reason it hangs up my computer and causes me to have to
> re-start.  The objective is to import multiple Excels workbooks with an
> inconsistent number of worksheets in each file.

> Any assistance will be greatly appreciated.

> Private Sub test_import_Click()

> Dim rs As New DAO.Recordset
> Dim xDir As String
> Dim xlApp As Excel.Application
> Dim xlBook As Excel.Workbook
> Dim iSheet As Integer, iRow As Integer, iCol As Integer

> Const MyPath = "C:\MyDocuments\"

> Set xlApp = New Excel.Application

> xDir = Dir(MyPath & "*.xls")

>     Do Until xDir = ""
>         Set xlBook = xlApp.Workbooks.Open(MyPath & xDir)
>         iSheet = 1 'this is the looping variable for the sheets in the
file
> xDir
>             Do Until iSheet > xlBook.Sheets.Count
>                 With xlBook.Sheets(iSheet)
>                     .Activate 'Activates the current sheet of the workbook
>                     iRow = 2  'This allows for row 1 to be a header row
>                     Do Until IsEmpty(.Cells(iRow, 1)) 'Append will stop
when
> an empty cell is encountered in column A'
>                         rs.AddNew 'Puts a new empty recrod in the access
> table
>                         For iCol = 1 To rs.Fields.Count - 1 'Prepare to
read
> the current row of the Excel sheet
>                             rs(iCol) = .Cells(iRow, iCol)
>                         Next iCol
>                         rs.Update
>                         iRow = iRow + 1
>                     Loop
>                 End With
>                 iSheet = iSheet + 1
>             Loop
>         xlBook.Close
>         xDir = Dir
>     Loop

> xlApp.Quit 'Exits Excel

> 'Good Housekeeping
> rs.Close
> cnn.Close
> Set rs = Nothing
> Set cnn = Nothing
> Set xlBook = Nothing
> Set xlApp = Nothing
> End Sub

> --
> Lydia M. Frank



Tue, 12 Nov 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Import Excel Worksheet(2)

2. Importing an Excel Worksheet

3. Import Excel worksheets via VB and DAO?

4. Newby question: import Excel worksheet

5. VB 4.0 Importing Data from Excel Worksheet into Access 2.0

6. Import Excel Worksheet into an Access Database

7. Newby question: import Excel worksheet

8. Deleting Rows Across Multiple Excel Worksheets

9. Using VB (or VBA) to call code in multiple worksheets of an Excel app

10. Export to Excel - Multiple Worksheets

11. How to import data that is inconsistent - PLEASE HELP IF YOU CAN

12. Using an Excel file/worksheet as data source

 

 
Powered by phpBB® Forum Software