TransferSpreadsheet only importing first Sheet

[GavZ]

Hi,

I cannot seem to adapt the following code to import all worksheets in all Excel Files in a directory. It wil only import the first sheet in every file:

    Const strPath As String = "C:\"
    Dim strFile As String
    Dim strFileList() As String '
    Dim intFile As Integer '


    strFile = Dir(strPath & "*.xls")
    While strFile <> ""

        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend

    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If

    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferSpreadsheet acImport, , _
        "t_temptable", strPath & strFileList(intFile), False
    Next
    MsgBox UBound(strFileList) & " Files were Imported"

Thanks in advance..

 

[vbaInet]

I'm not completely clear about what you're after but look at the Export to Excel functions in bob larson's website and see if one of them meets your needs:

http://www.btabdevelopment.com/ts/default.aspx?PageId=10

 

[GavZ]

Sorry i should have made it clear. i am trying to import all workboorks in a folder. the code i pasted sort of does this but only the first worksheet from each workbook whereas the files have up to 10.

THanks

 

[vbaInet]

How often will you be performing this exercise?

 

[GavZ]

fortnightly and it will be about 200 workbooks
cheers

 

[vbaInet]

It's beginning to sound like your db is not normalized. Or you're using the db for simply performing statistical or analytical functions?

What you need to do is loop through the Worksheets collection of that workbook and for each worksheet plug its name into the Range parameter of the TransferSpreadsheet method.

Perhaps this thread will give you some pointers:

http://www.access-programmers.co.uk/forums/showthread.php?t=217246&highlight=excel