Thread: Importing multiple sheet from excel to access vb script keeps on crashing

Hi,
i have this rather large excel workbook and im trying to import each sheet as a table in access. So I wrote a small script to do it, because doing it worksheet by worksheet is way to much work.
This is what I wrote:

Code:

Sub importar()

Dim libro As Excel.Workbook
Dim hoja As Excel.Worksheet
Dim xl As Excel.Application
 
 
 
Set xl = New Excel.Application
    Set libro = xl.Workbooks.Open("\\Scl012\riesgo_finan\DCC\Cred_comer\Menores_deud\Cred.Comerciales (menores Deudores) - 31-10-2008 al 31-01-2004.xls")
    
    xl.Calculation = xlCalculationManual
   ActiveWorkbook.PrecisionAsDisplayed = False
    xl.ErrorCheckingOptions.BackgroundChecking = False

    With libro
        For Each hoja In .Worksheets
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, hoja.Name, "\\Scl012\riesgo_finan\DCC\Cred_comer\Menores_deud\Cred.Comerciales (menores Deudores) - 31-10-2008 al 31-01-2004.xls", True, hoja.Name & "!A1:AQ55536"
        Next
    End With
     xl.Calculation = xlCalculationAutomatic
    ActiveWorkbook.PrecisionAsDisplayed = True
    xl.ErrorCheckingOptions.BackgroundChecking = True
 libro.Close False
End Sub

The drama comes when the script is importing the third worksheet, it just crashes claiming that it can't find the third worksheet (which makes little sense because hoja.name has the exact name of it) I imagine that there is something else wrong with the script because is running very, very slowly. Any ideas?

Perate

Your thread does not follow forum rules

Using code tags involves using the # icon button in the message window

Please wrap your code correctly