[AccessD] Preventing user prompts from Excel

try xlApp.ActiveWorkbook.SaveAs Filename:=atrFileName, FileFormat:=xlNormal
before you close

Jim Hale

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com]
Sent: Tuesday, July 13, 2004 2:45 PM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Preventing user prompts from Excel


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
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com