Open method of worksbooks class failed

[Ben]

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

 

[Jim Cone]

Ben,

The following code may get you closer.
Note that the creation/deletion of the new XL app has been moved
outside of the loop. That way it only has to be done once.

It was not clear as to what was being done to what workbook when.
I assumed you would open an existing workbook, make a new
workbook (using the first sheet in the old workbook as the template).
Close the original workbook, with out saving it, make some
changes to the new workbook, then save and close it.

Jim Cone
San Francisco, USA

'------------------------------------------
Sub GoThruThemFiles()

'Excel file object
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorkbookNew As Excel.Workbook
Dim lngIndex As Long

Set objExcel = New Excel.Application
'You may not want to do the next two lines?
objExcel.DisplayAlerts = True
objExcel.Visible = True

For Each fsoFile In fsoFolder.Files
'Check for proper file extension
If LCase(Right(fsoFile.Name, 4)) = ".xls" Then
lngIndex = lngIndex + 1
'Create the full file name
sFileName = sFolderImport & lngIndex & fsoFile.Name
DTSGlobalVariables("FileName").Value = sFileName
Set objWorkbook = objExcel.Workbooks.Open(fsoFile.Name)
'Create new workbook from copy of sheet
objWorkbook.Worksheets(1).Copy
Set objWorkbookNew = objExcel.ActiveWorkbook
'Close original workbook without any changes to it.
objWorkbook.Close savechanges:=False

MsgBox (sFileName & " has been created")
'Do secret stuff to new workbook
objWorkbookNew.Worksheets(1).Cells(1, 1).Value = "Secret"
'Save the new file and close it.
objWorkbookNew.Close savechanges:=True, Filename:=sFileName
Set objWorkbook = Nothing
Set objWorkbookNew = Nothing
End If
Next

MsgBox lngIndex & " new file(s) were created. "
objExcel.Quit
Set objExcel = Nothing
End Sub


"Ben" <ben_1_ AT hotmail DOT com> wrote in message
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

 

[GregR]

Jim, in using your code, I get a not defined error for
DTSGlobalVariables. TIA

Greg

 

[Jim Cone]

You had better ask Ben. The code he provided was only a partial.
The FileSystemObjects variables, DTSGlobalVariables
and others are undeclared. To get my test code to work I had to
arbitrarily declare them as I saw fit...
Dim DTSGlobalVariables as String

Jim Cone


Jim, in using your code, I get a not defined error for
DTSGlobalVariables. TIA
Greg

 

[Jim Cone]

Actually, I just commented out the line with DTSGlobalVariables.

Jim Cone


You had better ask Ben. The code he provided was only a partial.
The FileSystemObjects variables, DTSGlobalVariables
and others are undeclared. To get my test code to work I had to
arbitrarily declare them as I saw fit...
Dim DTSGlobalVariables as String

Jim Cone


Jim, in using your code, I get a not defined error for
DTSGlobalVariables. TIA
Greg

 

[Ben]

thanks jim, i will try that when i get into the office tommorow. I did leave
parts of the code out due to the sensitivity of how i pull data from these
spreadsheets into a sql server database. the dtsglobalvariables is a global
variable that i have set in sql server for use in what is known as a "data
transformation service"

again i will try the code, however i feel im going to run into the same
problem. The problem (as i have figured it to be) is that when i manually
open the file that the code fails on, i get a notification from excel saying
it has automatically renamed the spreadsheet because it was named porly
(which it wasnt). It seems to be because these sheets where created in excel
5.0. i just needed code that can open these spreadsheets, let excel
autorename the worksheet and then close and save the worksheet so that i can
open it again (through code) and not get that error. Im not concerned about
sheet naming cause my import routine renames all the sheets to their proper
names.

thanks again for any and all help!

ben