Need to import multiple excel files with different number of sheets into access table (1 Viewer)

[mirtomi]

Hi my friends,

I need a little help with some code I've come up to in order to import multiple excel files each of them with a different number of worksheets into an access table.
The procedure is called from an Access database.
The problem I have with the code is that when it encounters a workbook with only one worksheets (e.g. Sheet 1) it gives the error that "Sheet 2$" is not a valid name. When geting to a workbook with 2 sheets it says that "Sheet 3$" is not a valid name and so on and so forth.
Is there a way to "check" the number of sheets in the workbooks and when it has only one sheet to transfer it and go to the next file?

Below is the code:

    Sub ImportExcelFiles()
  Dim strFile As String 'Filename's
  Dim strFileList() As String ' File Array
  Dim intFile As Integer 'Number of files
  Dim filename As String
  Dim path As String

    DoCmd.SetWarnings False
    
    path = "D:\Tranzactii\"

    'Creates a look to build the file list

    strFile = Dir(path & "*.xls")

    While strFile <> ""

       'add files to the list

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

    Wend

    'Check for files

    If intFile = 0 Then

      MsgBox "No files found"

   End If

    'cycle through the list of files

    For intFile = 1 To UBound(strFileList)

      filename = path & strFileList(intFile)
              
      Dim x As Integer
      Dim strSheet As String
            On Error GoTo Handler
            
    'cycle through the sheets
      'Here is where i am having the problem
      'because some of the workbooks have 1 worksheet others
      'have 2 worksheets and some have more.
      'The code loops through the files and when encounters a
      'workbook that has only "Sheet 1" than I get the error that
      ' "Sheet 2$" is not a valid name. When the workbook has
      ' "Sheet 1" and "Sheet 2", I get the error that
      ' "Sheet 3$" is not a valid name/object

        For x = 1 To 15
        strSheet = "Sheet " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "Tranzactii", _
            filename, _
            True, _
            strSheet
        Next x
       
    Next intFile
    
    MsgBox "Import Complete!"
    Exit Sub
    

Handler:
    MsgBox Err.Description
    Exit Sub

    DoCmd.SetWarnings True

  End Sub

Thank you very much for your help in advance.

 

[Galaxiom]

Automate an Excel session, open the workbook and return:

ActiveWorkbook.Worksheets.Count

for the automated Excel Session

eg

MyExcelApp.ActiveWorkbook.Worksheets.Count

 

[mirtomi]

Thank you for the answer. Here's what I did do far:

      Dim WS_Count As Integer
      WS_Count = ActiveWorkbook.Worksheets.Count
      
      
      Dim x As Integer
      Dim strSheet As String
            On Error GoTo Handler

        For x = 1 To WS_Count
        Do...things

But I really don't know how to automate an Excel Session so the code can count the sheets. I'm kind of new to VB and the more specific the answers I get, the greater the help.

Thank you again.

 

[mirtomi]

I figured it out eventually. This does the trick:

   For intFile = 1 To UBound(strFileList)
    filename = path & strFileList(intFile)
     
    Workbooks.Open (filename)
    Dim wsCount As Integer
    Dim sheet As Worksheet
    
    For Each sheet In ActiveWorkbook.Worksheets
    wsCount = ActiveWorkbook.Worksheets.Count
    Next sheet
    
    Workbooks.Close
   
Dim x As Integer
      Dim strSheet As String
            On Error GoTo Handler
            
        For x = 1 To wsCount
        strSheet = "Sheet " & x & "!"
        DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel8, _
            "Tranzactii", _
            filename, _
            True, _
            strSheet
        Next x

Thank you again.