Late binding - RE: [AccessD] Preventing user prompts from Excel

LOL.  Because I am using late binding, and the xlNormal is a constant in the
excel library which I do NOT have referenced I cannot use that constant
directly.  I have to reference the library, look up that constant and see
what it really is (the integer value), then either declare that constant in
my own module or simply use that value as a "magic number".

This is another value that you get from early binding - the constants
defined in the lib are available to you to use in your programming.

John W. Colby

-----Original Message-----
From: Hale, Jim [mailto:Jim.Hale at fleetpride.com]
Sent: Tuesday, July 13, 2004 4:33 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com