[AccessD] Preventing user prompts from Excel

That is if the setwarnings false mentioned in earlier mail does not
work..;-) 

Pffff 

-----Oorspronkelijk bericht----- 
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens StaRKeY 
Verzonden: dinsdag 13 juli 2004 22:16 
Aan: 'Access Developers discussion and problem solving' 
Onderwerp: RE: [AccessD] Preventing user prompts from Excel 

John, you'd better use the filesystemobject if it's just renaming, set a
reference to Scripting and voila... you are able to check each filetype if
you want to loop a dir, rename,copy etc...

OR if it's just a sheet inside Excel (haven't been paying too much attention

here) then why don't you check the imported excel version and do a saveas
before closing? My guess is that will do the trick..

OR if the sheet is always the first then why not change the linked table
tablename in the connectstring (+ refresh) based on the name of the first

sheet?:-) 


Regards, 
Eric Starkenburg 

-----Oorspronkelijk bericht----- 
Van: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] Namens Colby, John 
Verzonden: dinsdag 13 juli 2004 21:45 
Aan: 'accessd at databaseadvisors.com' 
Onderwerp: [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 



  _____  

avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet besmet. 


Virus Gegevensbestand (VPS): 0429-0, 12-07-2004 Getest op: 13-7-2004
22:16:26 avast! auteursrecht (c) 2000-2004 ALWIL Software.



-- 
_______________________________________________ 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 



  _____  

avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet besmet. 


Virus Gegevensbestand (VPS): 0429-0, 12-07-2004
Getest op: 13-7-2004 22:25:32
avast! auteursrecht (c) 2000-2004 ALWIL Software.