[AccessD] TrasnferSpreadsheet failing

David McAfee davidmcafee at gmail.com
Tue Mar 17 17:37:01 CDT 2009


Not sure. The error you were getting sounded like something was open or in a
read only location.

I actually use this procedure (output parameter to launch is false, because
I add the grid then open):

Private Sub RGAsOver90aysExp_Click()
    Dim strmySql As String, strOutPutFile As String, objXL As Object, row As
Integer
    strmySql = "EXEC stpRptRGAsStep190Days"
    strOutPutFile = Me.Application.CurrentProject.Path &
"\RGAsOver90Days.xls"
    DoCmd.OutputTo acOutputStoredProcedure, strmySql, acFormatXLS,
strOutPutFile, False
    DoCmd.OutputTo acOutputQuery
    'Now, format the sheet
    Set objXL = CreateObject("Excel.Application")
    With objXL.Application
        .Visible = True
        .Workbooks.Open strOutPutFile

    objXL.Cells.EntireColumn.AutoFit

    row = objXL.CountA(objXL.Worksheets("EXEC
stpRptRGAsStep190Days").Range("A:A"))
    With objXL.Worksheets("EXEC stpRptRGAsStep190Days").Range(.Cells(1, 1),
.Cells(row, 9))
        'LineStyle = 1 = xlContinuous
        .Borders(7).LineStyle = 1 'xlEdgeLeft
        .Borders(8).LineStyle = 1 'xlEdgeTop
        .Borders(9).LineStyle = 1 'xlEdgeBottom
        .Borders(10).LineStyle = 1 'xlEdgeRight
        .Borders(11).LineStyle = 1 'xlInsideVertical
        .Borders(12).LineStyle = 1 'xlInsideHorizontal
    End With
End With
Set objXL = Nothing

End Sub




On Tue, Mar 17, 2009 at 3:26 PM, Rocky Smolin at Beach Access Software <
rockysmolin at bchacc.com> wrote:

> Yes, that works David.  Thanks.  But why? Or why not the other?
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
> Sent: Tuesday, March 17, 2009 3:09 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] TrasnferSpreadsheet failing
>
> Rocky, does this work for you?
>
> Dim strOutPutFile As String
>
> strOutPutFile = Me.Application.CurrentProject.Path &
> "\ExportedMailingList.xls"
>
> DoCmd.OutputTo acOutputQuery, "qryExportMailingList", acFormatXLS,
> strOutPutFile, True
>
>
> I usually trap for error 2302 in my error handler and inform them with a
> messagebox:
>
>  ElseIf Err.number = 2302 Then MsgBox "Unable to export to Excel file. Make
> sure you do not currently have that file open", , "Unable to export"
>
> I don't know what 3027 is. It could be similar.
>
> HTH
> David
>
> 2009/3/17 Rocky Smolin at Beach Access Software <rockysmolin at bchacc.com>
>
> > Dear List:
> >
> >
> >
> > I am trying to export data from a query to a spreadsheet.
> >
> > I get the error 3027 Cannot update. Database or object is read only.
> >
> > The line is: DoCmd.TransferSpreadsheet acExportDelim,
> > acSpreadsheetTypeExcel9, "qryExportMailingList", varFileName, True
> >
> > varfilename has the full path, name and extension.
> >
> >
> >
> > Any ideas what I'm doing wrong?
> >
> >
> >
> > MTIA
> >
> >
> >
> > Rocky Smolin
> >
> > Beach Access Software
> >
> > 858-259-4334
> >
> > www.e-z-mrp.com <http://www.e-z-mrp.com/>
> >
> > www.bchacc.com <http://www.bchacc.com/>
> >
> >
> >
> >
> >
> >
> >
> > --
> > 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list