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 |
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 |