[AccessD] Exporting and opening excel spreadsheet

Rusty,

I found that trying this would cause the problem you described.
    strSql = "Select * from vUnits where UnitID between 19445 and 19448"
     rs.Open strSql, cn, adOpenKeyset, adLockOptimistic
    DoCmd.OutputTo acOutputServerView, strSql, acFormatXLS, "C:\test.xls", 
True

Because it tried to name the sheet the strSql string. My solution was to 
create a view on the fly then name the view in OutputTo call

    Dim strView As String, strPeriod As String ,strSqlCmd As String
    Dim cn As New ADODB.Connection
    Set cn = CurrentProject.AccessConnection
    strView = "vTemp" & Trim(glbStrUserName)  'Create a personal instance
    strPeriod = "0511"
    strSqlCmd = "if object_id('" & strView & "') is not null drop view dbo." 
& strView
    cn.Execute strSqlCmd
    strSqlCmd = "create view dbo." & strView & " as select * from 
vExcelOTReport where      BillingPeriod = '" & strPeriod & "'"
    cn.Execute strSqlCmd

then

 docmd.outputTo acOutputServerView ,strView ", acformatXLS, 
"c:\Somefilename.XLS"

Seemed to work ok

Hope this helps.

Bill
----- Original Message ----- 
From: <rusty.hammond at cpiqpc.com>
To: <accessd at databaseadvisors.com>
Sent: Friday, April 28, 2006 2:27 PM
Subject: [AccessD] Exporting and opening excel spreadsheet


We are in the process of slowly upgrading to Office 2003 but still mainly
use Access 97.  In one of my Access 97 apps I'm using the OutputTo command
to export a report to Excel then via code I'm opening the excel spreadsheet
and running an Excel macro to clean up the spreadsheet.  This works fine
with Excel 97 but when Excel 2003 tries to open the spreadsheet I get an
error that the open method fails in the excel object.  If I go to Excel 2003
and manually open the spreadsheet I get a message that tells me Excel
automatically repaired the sheet and the problem is it "renamed invalid
sheet name".  The sheet name is just Sheet1 as far as I can tell.  Any
ideas?

TIA

Rusty Hammond
IT Dept. - B-20
CPI Qualified Plan Consultants, Inc.
(620) 793-8473 ext. 416
rusty.hammond at cpiqpc.com


**********************************************************************
WARNING: All e-mail sent to and from this address will be received,
scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review
by, and/or disclosure to, someone other than the recipient.
**********************************************************************
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com