docmd.transferspreadsheet help??

[Alan]

Hi
Can this methods take the results of one of my report

i have a report name Report_Monthly Sales Report which when someone clicks a
customer and month they want, press preview button the report then show a
preview of it.

what i've got then is a command button so they can export to excel with
formattting

but everytime i press it i get an error saying

cannnot find object 'Report_Monthly Sales Report' but this is the name of
the report i want to send.

is this possible

my code below

Private Sub Command20_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"Report_Monthly Sales Report",
"F:\Accounts\Projects\Analysis\Billlings\DSICMM\Access\MonthlySalesReport",
True
Call
ModifyExportedExcelFileFormats("\\F:\Accounts\Projects\Analysis\Billlings\DSICMM\Accessz\MonthlySalesReport", "Report_Monthly Sales Report")
End Sub

 

[Daniel Pineault]

From the Help file

*******************
The TransferSpreadsheet method carries out the TransferSpreadsheet action in
Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the
Applies To list.

TransferType Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
acSpreadsheetTypeExcel3
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel8 default
acSpreadsheetTypeExcel9 default
acSpreadsheetTypeLotusWJ2 - Japanese version only
acSpreadsheetTypeLotusWK1
acSpreadsheetTypeLotusWK3
acSpreadsheetTypeLotusWK4
Note You can import from and link to Lotus .WK4 files, but you can't export
Microsoft Access data to this spreadsheet format. Microsoft Access also no
longer supports importing, exporting, or linking data from Lotus .WKS or
Microsoft Excel version 2.0 spreadsheets by using this method.

If you leave this argument blank, the default constant
(acSpreadsheetTypeExcel8) is assumed.


TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import spreadsheet data into, export
spreadsheet data from, or link spreadsheet data to, or the Microsoft Access
select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and
path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the
spreadsheet as field names when importing or linking. Use False (0) to treat
the first row of the spreadsheet as normal data. If you leave this argument
blank, the default (False) is assumed. When you export Microsoft Access table
or select query data to a spreadsheet, the field names are inserted into the
first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet. When
you export to a spreadsheet, you must leave this argument blank. If you enter
a range, the export will fail.

UseOA Optional Variant.
*******************

As such you can see the TableName variable is clearly limited to Tables or
Queries. So, no, you cannot use it with your report as you are trying. You
can use it with the underlying report query or table however.

Your other option would be to create your own routine to automate Exel and
add the formatting you need.

If you explain a little more, perhaps someone here could give you a better
approach.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Alan]

I have a dialog box where people can click on customer name and month for
sales during that month,

they select the report they want which is monthly and press perview which
then pulls all the information from the Sales analysis table,

this is the code for my monthly report

Public Sub Report_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strSql As String

If IsNull(TempVars![Display]) Or IsNull(TempVars![Year]) Or
IsNull(TempVars![Month]) Or IsNull(TempVars![Group By]) Then
DoCmd.OpenForm "Actual Report"
Cancel = True
Exit Sub
End If


strSql = "SELECT [Year]"
strSql = strSql & ", [Month]"
'strSQL = strSQL & ", [Customer No]"
strSql = strSql & ", ([" & TempVars![Display] & "]) AS
SalesGroupingField" & ",([" & TempVars![Display2] & "]) AS Cust"
strSql = strSql & ", Sum([AmountActual]) AS [Total Sales]" & ",
SUM([Amount1A11F]) AS [1A11F]" & ", Sum([Amount6A6F]) AS [6A6F]"
strSql = strSql & ", first([Sales Analysis].[Posting Date Month]) AS
[Month Name]"
strSql = strSql & " FROM [Sales Analysis] "
strSql = strSql & " Where [Month]=" & TempVars![Month] & " AND [Year]="
& TempVars![Year]
strSql = strSql & " GROUP BY [Year], [Month], [" & TempVars![Group By] &
"];"

Me.RecordSource = strSql
Me.SalesGroupingField_Label.Caption = TempVars![Display]

Done:
Exit Sub
ErrorHandler:
' Resume statement will be hit when debugging
If eh.LogError("Monthly Sales Report_Open", "strSQL = " & strSql) Then
Resume
Else
Cancel = True
End If

end sub

ideally the report is all formatted to the way i like it and want it to
format the same in excel but i know this cannot be done,

so i used this a docmd.spreadsheet
to see if i could pull the report then used this code below to do a simple
format for now


Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
On Error GoTo Proc_Error

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

With xlApp
.Application.Sheets(sSheet).Select
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True
.Application.range("A1").Select
.Application.Selection.AutoFilter
.Application.Cells.Select
.Application.Selection.Columns.AutoFit
.Application.range("A1").Select
.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With




Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
Exit Sub



End Sub

But it doesn't like report, how do i get around this