Importing entire workbook into Access using VBA 
Author Message
 Importing entire workbook into Access using VBA

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 into Access on-click.

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  
 Importing entire workbook into Access using VBA
I looked up and down your code and do not see where you open the recordset.
The usual way:

Dim rs as DAO.Recordset
set rs = DBEngine(0)(0).OpenRecordSet("TargetTableHere", dbOpenDynaset)

I can't think right off the top what "Dim rs As New DAO.Recordset" will do for you.
You're not referencing a Table so no Fields, you'd end up in space somewhere.

Steve

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 into Access on-click.

> 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  
 
 [ 2 post ] 

 Relevant Pages 

1. Using VBA to Import a Range from an Excel Workbook

2. VBA: Excel workbook to workbook transfer

3. Save current workbook using VBA

4. Excel HRESULT: 0x800A03EC when using Workbooks.open or Workbooks._opentext in C#

5. Can't print entire workbook

6. VB or Excel VBA, accessing list of all the sheets in a workbook

7. Using Excel workbooks and sheet from Access

8. Using VB5 to import data to access without using the Data control

9. Importing from a Spreadsheet using VBA

10. Importing modules, forms, reports using VBA

11. Problems using VBA to link or DAO to import Excel file into Access97

12. Best way to import large contact list using VBA

 

 
Powered by phpBB® Forum Software