regarding choking of access while importing excel files

[ritimajain]

hi
i have a code to import excel files in access which works fine but my problem is if data is very large access choke while importing, i want that it will import only two sheets at a time and when importing of that is over , it will import next two and so on.. how can i put this in a loop? please help
my code is ...
Private Sub Import_New_Dump_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim filename As String
Dim blnHasFieldNames As Boolean



' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True


strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then


MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If


' Replace tablename with the real name of the table into which
' the data are to be imported
strFile = Dir(strPath & "\*.xls")
Do While Len(strFile) > 0
filename = strPath & "\" & strFile


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN1", filename, True, "CHADMIN1$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN3", filename, True, "CHADMIN3$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN4", filename, True, "CHADMIN4$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CLS", filename, True, "CLS$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "HCS1", filename, True, "HCS1$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ICM", filename, True, "ICM$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "IHO", filename, True, "IHO$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "INTERNAL", filename, True, "INTERNAL$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "LOCATING1", filename, True, "LOCATING1$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "LOCATING2", filename, True, "LOCATING2$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "POWER", filename, True, "POWER$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "POWERCONTROL1", filename, True, "POWERCONTROL1$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "POWERCONTROL2", filename, True, "POWERCONTROL2$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "STATUS1", filename, True, "STATUS$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SUBCELL", filename, True, "SUBCELL$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "SYSINFO", filename, True, "SYSINFO1$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TRX", filename, True, "TRX$"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TX", filename, True, "TX$"

' Next intFile
strFile = Dir()
Loop

MsgBox "Import Complete"

DoCmd.SetWarnings True


' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile




End Sub

[ssanfu]

This is just a shot in the dark, but with that many imports I would try adding DoEvents every two imports.

<snip>
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN1", filename, True, "CHADMIN1$"
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN3", filename, True, "CHADMIN3$"
     
  DoEvents
     
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CHADMIN4", filename, True, "CHADMIN4$"
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "CLS", filename, True, "CLS$"
   
  DoEvents
     
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "HCS1", filename, True, "HCS1$"
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ICM", filename, True, "ICM$"

  DoEvents

<snip>