Import multiple worksheets into single Access Table using VBA

[denosaur]

I have an Excel spreadsheet that has 212 worksheets and I need to import (or copy) the data from each of the worksheets into a single table in Access. I figured if I could specify the worksheet to import, I could loop through the worksheets importing the data for each one, but the DoCmd.TransferSpreadsheet method doesn't seem to let me import anything except Sheet1 (or specify a worksheet). Can anyone offer me some guidance or suggestuions for tackling this?

Thanks,
Dean

 

[PHV]

or specify a worksheet
So, where is the problem ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[denosaur]

I do not know how to get to all the sheets in the workbook...only Sheet1. There are 212 sheets (Sheet1, Sheet2, etc.) and I need all of them. A point in the right direction would help a lot.

 

[PHV]

I do not know how to get to all the sheets in the workbook...only Sheet1
What is your actual code for getting Sheet1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[denosaur]

I figured out how to do what I needed to do.

Below is my code. It worked just fine. NOTE: In the first For... loop, I used Step 2 because it turns out I only needed the records from every other sheet beginning with sheet1. I also only needed the data from columns A and D from the first 51 records, leave the header row, from every other sheet.

    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    Dim sFile As String
    Dim rs As Recordset
    Dim r, s As Integer
    sFile = "G:\Clients\CEB\Log I0290\Surgical_Schedule_Jan2011.xls"
    
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Open(sFile)
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM SR10")
        
    For s = 1 To 212 Step 2
        Set oSheet = oBook.Worksheets(s)
            For r = 2 To 51
                rs.AddNew
                rs!Code = oSheet.Range("A" & r).Value
                rs!FeeAmount = oSheet.Range("D" & r).Value
                rs.Update
            Next r
        Next s
            
    oExcel.Quit
    Set rs = Nothing

Thanks.

 

[traingamer]

Try clearing your variable before reuse (not tested):

    For s = 1 To 212 Step 2
        Set oSheet = oBook.Worksheets(s)
            For r = 2 To 51
                rs.AddNew
                rs!Code = oSheet.Range("A" & r).Value
                rs!FeeAmount = oSheet.Range("D" & r).Value
                rs.Update
            Next r
        [blue]Set oSheet = Nothing[/blue]
    Next s

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[traingamer]

Ignore that last post - I'm learning how not to read in my advanced age.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[kjv1611]

I would add this to the end, if you've not already:

  Set s = Nothing
  Set oBook = Nothing
  oExcel.Quit
  Set oExcel = Nothing

You've got the quit part, but I'd put the other around it.. if it were me.

 

[madelca100]

Question - how would you read all the rows in each worksheet if you don't know how many rows there are? Obviously each worksheet could have a different number of rows.

Assume you can't test for a blank cell, but must read until you get to a blank row in the worksheet.

 

[kjv1611]

There are a couple of ways to do it.

Try this search to find multiple online references/walkthroughs/code.