Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.








Sponsored Links:

Related Messages:
Where To Use DoCmd.OutputTo To Create An Excel Spreadsheet
I want to run a report, I need to provide an option to export the output to an excel file or provide a print view. Where would I put the DoCmd.OutputTo command in the report to acheive this??

thanks

View Replies !   View Related
"DoCmd.OutputTo" To Excel Truncates Character/ Field
Hi all,

I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.

Can please somebody help
Thanks
Claude

View Replies !   View Related
Export A Report From Access 2003 To Excel 2003 Using The Outputto Method
I am using the outputto method to export an access report to excel. Theres no problem there except whenever i close the excel file i keep getting the message: WonLoss is a Microsoft Excel 5.0/95 Workbook. Do you want to overwrite it with the latest Excel Format.
Is there a way to avoid this popup message?

This is the code i am using.

Private Sub cmdTeam_Click()
DoCmd.OutputTo acReport, "rptWonLoss", "MicrosoftExcel 97-10(*.xls)", "C:BowlingWeeklyWonLoss.xls", True, ""
End Sub

Thanks

View Replies !   View Related
AcFormatXLS In DoCmd.OutputTo
Hi all,

To export a file to MS excel from my form, i used the command

DoCmd.OutputTo acOutputQuery, langName & partName, acFormatXLS, exportPath, False

however, i realised that the format of the excel spreadsheet (excel version 5.0/95) that i have exported is slightly different from the format of the spreadsheet if i had exported by clikcing on FILE, EXPORT...

how can i specify the excel version for the exported file? i wan it to be in the latest excel version else i am unable to perform some of the marcos i have written in the latest excel format?

Thks FT

View Replies !   View Related
Export Query TO Excel
I have a query in access 2002 named "FMC DETAIL / MEMBER" when i export to excel 2002 with named "FMC DETAIL_member" the export says "Path is invalid"

My main point is that "/" should be replace with "_" in file name automatically.
In access 97 query named "FMC DETAIL / MEMBER" when export to excel the "/" is renamed to "_" automatically.

Any Help would be appreciated.........

View Replies !   View Related
Export A Query To Excel
Hi! I know queries can be exported to excel by the option "File > Export".

The fact is that I'm using "order by" options and some functions like "Month", and "Export" options appears disabled, is there a way to export this query to Excel? :confused:

View Replies !   View Related
Export Query To Excel
Hi there,

I have a button on a form and when I press it I would like this to happen:
Some code will be run that makes a copy of an templatefile (Excel), puts the values that comes from my query into the document and the saves it with a name that comes from two textboxes. I would also like to be able to put in some other text in the document, like two dates that I have on my form. Does anyone know how to do this ???

View Replies !   View Related
EXCEL Export And Corresponding Form/query
I have a single table created in access with different supplier information (supplier name, address, item supplied, etc) -- about 1500 records.

i need to create a form of some sort that will allow users to extract data on either the specifc supplier name (from a pulldown menu) and the items that they supply OR be able to type in (in an input window) the item they are looking for (would use the criteria Like "X" where x is some part of the name of the component) and then see which suppliers have supplied that specific item.

it would also be nice if the user could then select by a check box or something which other attributes be displayed with the data.

my thought is that i need to create some sort of form that takes the information that the user inputs from the methods specified above and then dumps that information in to a query which then extracts the data.

then, is there a way to dump that extracted data in to an excel spreadsheet?

this is the first time i have done something like this, so i really havent got much of a clue.. any help or pointers that you can lend would be MUCH appreciated. thanks so much in advance.

View Replies !   View Related
Export Access Query To Excel
Folks,

I have a form with combo box (cboProjForRptSeltn ) and two command buttons. The combo box is filled with name of the reports. The combo box has two columns, column 1 is the name of the report and column 2 is the bound column which also is the link field (primary key) to limit the records.

When the user selects an item from cbo box and click the "Preview Risk Table" command button it will open a report in preview mode. The report's "Record Source" property is set to a UNION query (qryRprtRskTbl) which pull the records from several tables. The report's "Filter" property is set to the following criteria:
“qryRprtRskTbl.P.intProjectId=Forms!frmReportSelec tionBlrR1!cboProjForRptSeltn”

The above criteria is nothing but filter based on the value of the cbo box.

The click event procedure is as follows:
Private Sub cmdPreviewRprt_Click()
Dim strDocName As String
strDocName = "rptRskTblProjectWise"
DoCmd.OpenReport strDocName, acPreview, "qryRprtRskTblFilter_r1"
End Sub
"qryRprtRskTblFilter_r1" is another query out of the UNION query I mentioned above (qryRprtRskTbl). qryRprtRskTblFilter_r1 is pulling all the records from qryRprtRskTbl which meets the projectID field selected in cbo box, which is also the same as the "Filter" property value of the report as indicated above.

Everything works fine with cbo box and "Preview Risk Table" command button. It just pull all the records for the project selected under cbo box and display it as report in preview mode. Looks great!!

Here's my problem. I wanted to export the same report that was previewed by the user to Excel. For this I am using, another command button called “Export to Excel”. The click event of this procedure is as shown below:

Private Sub cmdExportToExcel_Click()
On Error Resume Next
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook

Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSearch As String
'Dim strSQL As String

'strSearch = Me![cboProjForRptSeltn]

strQueryName = "qryRprtRskTblFilter_r1"
'strQueryName = "qryEffcyAllProjtsForRprt"

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)


'Loop through the fileds collection and make each field name a column heading in Excel
Set xlSheet = xlWorkbook.Sheets(1)
For lvlColumn = 0 To objRST.Fields.Count - 1
xlSheet.Cells(1, lvlColumn + 1).Value = objRST.Fields(lvlColumn).Name
Next
'Change the font to bold for header row
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True

' I have some codes here for formatting Excel cells …

'Send data from Recordset out to Excel
With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = Left(strQueryName, 31)
End With

Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

End Sub

When I click the command button, it loads an instance of Excel and adds a workbook under the query name "qryRprtRskTblFilter_r1", but no data.

If I use another query say, "qryEffcyAllProjtsForRprt", which has no connection to cbo box value then it is cool, exports all the data to Excel without any problem.

I guess, you folks understand what I am trying to achieve here. Basically, I wanted to give the user some flexibility, either they can view the data as Access report or Export to Excel with same formatting feature and add more later if they want after exporting, as they see on Access report preview.

I have attached some of the query files I have described here. May be I can clarify more down the road, if necessary.

My bottom line question is: why the “qryRprtRskTblFilter_r1” query runs perfectly on Access reports but not when I want to run to export to Excel?

I don’t want to use the TransferSpreadsheet or outputTo method of docmd object, because I wanted to do some formatting before I export to excel.

Any help is greatly appreciated.

Thanks

ShanVel

View Replies !   View Related
Aggregate Query (Export To Excel)
Hi Everyone,

I am working on a Sales Analysis db, which pulls from:

tSales - Sales Figures (40k records)
tBuildUnit - Bom Build Units (8k records - one per product)
tBom - Bills of Material - All Components (71k records)
tUom - Units of Measure for Products (80k records - 10 per product)

It is quite basic in theory, it looks at a product sold, goes to tBom to get the cost of the raw materials.

I have a query that sums the components by their group, distinguishing between material and labour cost by the component group (cGrp) which is 'WORK' for labour and <>"WORK for materials. I can then sum the cost of the materials for a given product.

Anyway to cut to the issue in hand.

I export from our system (LM4 on btrieve on novell 3.2) into CSVs then manipulate those


And basically i want to have:

Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 350 420 230 190 250
Exodus 0 100 0 0 60 0
Revelation 200 300 140 200

we have 220 customers in this recordset. so i would like just 220 rows.


My data that i export shows every transaction (so i have 40k records)

So i used a aggregate query, grouping by customer account and summing the value, material, and labour fields, however this gives me each customer three times, one per year e.g.:

Cust______Sale04__Sale05__Sale06__Mat04__Mat05__Ma t06
Genesis 400 0 0 230 0 0
Genesis 0 350 0 0 190 0
Genesis 0 0 420 0 0 250

How do i get one what i describe above?

After the query runs i want to export to excel with:

DoCmd.OutputTo acOutputQuery, "qGrouping", acFormatXLS, "d:Output.xls", True


Thanks

Graham
(Carpenter by trade, now in construction acoustics and really shouldn't be doing anything with access!)

View Replies !   View Related
DoCmd.TransferSpreadsheet Does Not Export All Fields
I used the DoCmd.TransferSpreadsheet method to export from an Access 2003 table to Excel. The table had around 440 fields but only 230 (column iv) got exported.

Does anybody know whether there is a limitation on the number of fields that can be exported.

Thanks
Claude

View Replies !   View Related
Export To Excel, Update Two Fields In A Query Question
We have a report that is generated monthly to management on Excel. We Export the data from an Access query to Excel and at that time the user has to update the Month Reported and Year Reported fields on each record within that query.

Can I set up something to where the user can click a button or something and Access do all of this automatically?

Example: May 2005 was just sent out and we exported the data from the query to Excel and went back into the query and chose update query and had Access fill in May (in the month reported field) and 2005 (in the year reported field) on all of the records.

Any help is appreciated, thanks...

View Replies !   View Related
Export Pivot Table Query View In Excel
Hello,

I need to export a Pivot Table view to Excel. The issue is with Calculated Total columns which are not exported in excel. Do you know if there is any way I can export the Calculated columns in excel?

Thank you.

View Replies !   View Related
Export Query Results To Excel Incuding Subdatasheet
I have a query with a subdatasheet that is linked by a common key (Staff_ID). The first query displays aggregate information on each Staff_ID (i.e. Total hours worked, Total hours pay time off ..). The subdatasheet contains every record used to create the aggregate query. The query creates a nice executive view of the time sheet information and gives the user an easy way to drill down into the raw data.

My dilemma: How do I export the query results to Excel and keep the subdatasheet in the drill down format?

View Replies !   View Related
Export To Excel, Update Two Fields In A Query Question
We have a report that is generated monthly to management on Excel. We Export the data from an Access query to Excel and at that time the user has to update the Month Reported and Year Reported fields on each record within that query.

Can I set up something to where the user can click a button or something and Access do all of this automatically?

Example: May 2005 was just sent out and we exported the data from the query to Excel and went back into the query and chose update query and had Access fill in May (in the month reported field) and 2005 (in the year reported field) on all of the records.

Any help is appreciated, thanks...

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved