[AccessD] Preventing user prompts from Excel

That's what I like, a man with multiple valid suggestions.

The answers in all cases is simply that I have always just opened the damned
spreadsheet and renamed the sheet to what I wanted.  I guess I could open
the sheet, close it, open the link and edit that.

I am getting a spreadsheet from a client, attached to an email.  I use
outlook (withevents) to copy the email to a specific email folder (using a
rule) then raise an event for each attachment.  My code in another class
figures out what to do with the attachment including just using the messages
.save method to put it in a folder on the disk.  I then take that saved
email and copy it to another location with a common name (always the same),
open the spreadsheet (if it is a spreadsheet, some aren't) and rename the
interior sheet since clients can NEVER be expected to name the sheets the
same thing from day to day).  Having done all that I have a spreadsheet of a
given name with an interior sheet of a given name and the link just works.  

The alternative is to (and I have done this also) save the sheet, get the
interior sheet name, open the table def, and edit the filename and sheet
name portions and save the tabledef back again.

Both do work, both require Excel automation to open the sheet.

Anyway, thanks for the suggestions.

John W. Colby 
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of StaRKeY
Sent: Tuesday, July 13, 2004 4:16 PM
To: 'Access Developers discussion and problem solving'
Subject: 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