Colby, John
JColby at dispec.com
Tue Jul 13 14:44:45 CDT 2004
I am doing imports from data sent to us in Excel spreadsheets which I Link to the db then build queries on the resulting "table". In order to make sure that the spreadsheet is named something consistent (so that the link functions correctly), I am using the following function to rename a spreadsheet in Excel. Function XLRenameSpreadsheet(strFileName As String, strTblName As String) On Error GoTo Err_XLRenameSpreadsheet Dim xlApp As Object Dim xlWs As Object Set xlApp = CreateObject("Excel.application") xlApp.Workbooks.Open strFileName Set xlWs = xlApp.Workbooks(1).Worksheets(1) xlWs.Name = strTblName Exit_XLRenameSpreadsheet: On Error Resume Next xlApp.Workbooks(1).Close True Set xlWs = Nothing If Not (xlApp Is Nothing) Then xlApp.Close: Set xlApp = Nothing Exit Function Err_XLRenameSpreadsheet: MsgBox Err.Description, , "Error in Function basUtils.XLRenameSpreadsheet" Resume Exit_XLRenameSpreadsheet Resume 0 '.FOR TROUBLESHOOTING End Function Unfortunately I get a message: "XXX.XLS is a Microsoft Excel 5.0/95 workbook. Do you want to overwrite it with the latest Excel format" when I try and do the save xlApp.Workbooks(1).Close True Since this is an automated process there is no one around to answer yes (Excel can be so stupid sometimes). Is there any way to force a yes (or no, I don't really much care what the format is!) JWC