TransferSpreadsheet destroys Excel

[AlenaMezh]

Hello! My Code destroys Excel's normal work.After running this code I
acan' open excel file as normal. Do you know why? Please, answer.

Public Function import_test_1()
Dim objXL As Object
Dim objWB As Object
'Dim objXL As Excel.Application
'Dim objWB As Excel.Workbook
Dim strFile As String
Dim xlPath As String
Dim xlFile As String
Dim i As Integer

xlPath = "H:\ANNETTEB\financial statements as of feb 2008\TEMPLATES\"
'
'************************************************* ************************************************** ****
' 1- open each source file
' 2- insert 1 new sheet=template (give the same name to all of them!)
' 3- copy "outputs' sheet and PAste Special (Value,Skip
Blamks,Transposal)into "TEMPLATE" sheet
' 4 save file
' 5-after all sources are prepared- run
docmd.transferSpreadsheet ...for this folder
' 6 then open just created new table and edit it
'
'************************************************* ************************************************** ****

xlFile = Dir(xlPath & "*.xls")
'
i = 1

Set objXL = CreateObject("Excel.Application")
While xlFile <""

strFile = xlPath & xlFile

Set objWB = objXL.Workbooks.Open(strFile)

' For i = 1 To objWB.Worksheets.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
objWB.Worksheets(3).Name & i, strFile, True, objWB.Worksheets(3).Name
& "!"
' DoCmd.TransferDatabase ,,,,
' Next i
objWB.Close
'objXL.Quit
xlFile = Dir
i = i + 1
Wend
objXL.Quit
Set objWB = Nothing
Set objXL = Nothing
End Function

[Rich P]

Here is the catch with TransferSpreadsheet - you can only use it once
per workbook. You are trying to use it in a loop - won't work. If your
data is small (a few dozen lines per sheet) you may get away with just
using Automation - which it appears to were trying to combine automation
with TransferSpreadsheet. But you have to use the Excel Range object.
If you have more than a few dozen rows which you need to transfer - then
you need to use ADO. Look at MSDN on ADO usage with Excel. ADO is way
faster than automation for transferring large amouhnts of data from
Access to Excel, but a bit more sophisticated than Automation.
*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Upon re-reading your post - it looks like you are trying to import data
from Excel. In that case you can use automation and the Excel Range
object to select the UsedRange. Then you can use DAO code (native to
Access) to loop through the Range object and write the data to a table.
This way you can loop through the Excel Sheets collection.

Rich

*** Sent via Developersdex http://www.developersdex.com ***