Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
Jim, in using your code, I get a not defined error for
DTSGlobalVariables. TIA Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
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 "GregR" wrote in message oups.com... Jim, in using your code, I get a not defined error for DTSGlobalVariables. TIA Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
Actually, I just commented out the line with DTSGlobalVariables.
Jim Cone "Jim Cone" wrote in message ... 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 "GregR" wrote in message oups.com... Jim, in using your code, I get a not defined error for DTSGlobalVariables. TIA Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open method of worksbooks class failed
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 "Jim Cone" wrote: Actually, I just commented out the line with DTSGlobalVariables. Jim Cone "Jim Cone" wrote in message ... 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 "GregR" wrote in message oups.com... Jim, in using your code, I get a not defined error for DTSGlobalVariables. TIA Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill method of range class failed | Excel Discussion (Misc queries) | |||
Autofill method of range class failed - sometimes | Excel Programming | |||
Insert method of range class failed | Excel Programming | |||
Insert method of range class failed | Excel Programming | |||
Open Method of workbooks class failed | Excel Programming |