I am trying to read all excel files in a directory, and the code that opens
the files is pasted below. When i run the code some excel spreadsheets open
while others give me the error: "open method of workbooks class failed"

I have narrowed the problem down to the fact that when i manually open the
files that "fail" i get a message saying Microsft Excel has made repairs to
the spread sheet: "Renamed invalid sheet name". if i save this new sheet, it
opens fine with the below code.

My question is, how can i get the code to open, "ok" the repairs, close and
save the spreadsheet then open it again to be read? I am trying to automate
this whole process where these sheets are being created by one process and
this second process is importing them into sql server. This needs to be
completely automated.

Thanks for any and all help!
Ben

--code to open excel files....specifics on reading/parsing file have been
removed for security reasons --

For Each fsoFile in fsoFolder.Files
'Check for proper file extension
If LCase(Right(fsoFile.Name, 4)) = ".xls" Then

'Create the full file name
sFileName = sFolderImport & fsoFile.Name
DTSGlobalVariables("FileName").Value = sFileName

'Excel file object
dim objExcel

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = true

'Excel workbook object
dim objWorkbook
objExcel.Visible = True

'Set objWorkbook = objExcel.Open (sFileName)

objExcel.Workbooks.open sFileName
set objWorkbook = objExcel.ActiveWorkbook.Worksheets(1)
msgBox (sFileName & " has been opened")

'Save any changes
objExcel.Save

'Close the file and excel
objExcel.Quit

End If
Next

If all you want is to loop over the Excel files then why would you need the
Excel object model? What are you trying to do with the Excel spreadsheets?

Allan

"Ben" <ben_1_ AT hotmail DOT com> wrote

im importing their data for use in a sql backend. i left out the import and
parsing code

"Allan Mitchell" wrote: