[AccessD] Preventing user prompts from Excel

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



More information about the AccessD mailing list