If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
docmd.transferspreadsheet help??
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\Ac cess\MonthlySalesReport", True Call ModifyExportedExcelFileFormats("\\F:\Accounts\Proj ects\Analysis\Billlings\DSICMM\Accessz\MonthlySale sReport", "Report_Monthly Sales Report") End Sub |
#2
|
|||
|
|||
docmd.transferspreadsheet help??
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" wrote: 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\Ac cess\MonthlySalesReport", True Call ModifyExportedExcelFileFormats("\\F:\Accounts\Proj ects\Analysis\Billlings\DSICMM\Accessz\MonthlySale sReport", "Report_Monthly Sales Report") End Sub |
#3
|
|||
|
|||
docmd.transferspreadsheet help??
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 "Daniel Pineault" wrote: 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" wrote: 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\Ac cess\MonthlySalesReport", True Call ModifyExportedExcelFileFormats("\\F:\Accounts\Proj ects\Analysis\Billlings\DSICMM\Accessz\MonthlySale sReport", "Report_Monthly Sales Report") End Sub |
Thread Tools | |
Display Modes | |
|
|