 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Macros / February 2008OutputTo to Excel question
Hello: I guess this question is a combination of Exporting data/Reports/Macros.... I have used Access to write a Check Printing application. I now have a report where the user will enter a set of date ranges and the report will pull all the checks wrote within that date range. It will also combine the individual transactions under each check and provide the total amount for the checks using Sum([Individual Amounts]) which will workout the pluses/minuses. The report view works good and displays the data correctly. I also allowed the user to be able to output the report data into an Excel file. I used a button to trigger a Macro that runs OutputTo and I chose the Report as the object and MS Excel as the object type and let the user select the file name. One thing puzzles me is in the output Excel file. The total amount for the Check pulled is supposed to be in Currency format, and shows up that way in the report with $ sign and all. However, in the Excel file generated the column that had the totals are all showing General Numbers format. I've gone back and looked at my reports/tables designs and this field is listed as Currency. But for some reason, the total that appeared correctly in the report view didn't get its data format exported into Excel. What could I be missing? Thanks in advance. Shohoku79 -- Shohoku, Save a query that pulls the same data as the report. In the query add an extra column that formats the Total as currency. In this column put TotalAmount:CCur([TotalFieldName]) Uncheck the show checkbox for the original Total column Switch the query to datasheet view and check that the total is formatted the way you want. Export the query instead of the report. Jeanette Cunningham > Hello: > [quoted text clipped - 31 lines] > > Shohoku79 Hi Jeanette: Thank you for your response, I tried your suggestion and attempted to grab the result from a query. Except my transactions table does not store the sums at all, all the end results (as printed on Cks/Reports) were done by summing up "Individual Amounts" or transactions associated with a CK#. Hence the query for this field is written as Field:Total: Individual Amount Table: Transactions Total: Sum However, once I trigger the Macro to OutputTo the Query into Excel, it gives me the error message: "File Error. Some Number Formats may have been lost." and I can click OK to continue. The result looks like it does transferred over the $ sign and all. However, it looks like the format for this column in Excel is "Custom." Now, if I were to run the Query by itself, the output will be shown in Datasheet view. If I use the File->Export and choose to Export that Query as Excel, the result will not display the file error message, but the numbers displayed will be in "Currency" as it should be. This would be the same if I were to right click on Query and choose Export from the default database menu. So it looks like if I were to use Macro/OutputTo, I'd get the format error message, but if I manually Export the Query, I won't. I would like to click a button to automate the process of Exporting the Query into Excel format (autostart enabled if possible), so it looks like it will involve VBscripting in the backend. How should I go about approaching that? Thanks again, Shohoku  Signature> Shohoku, > [quoted text clipped - 45 lines] > > > > Shohoku79 I can't help you with the macro error message as I don't use macros. I wouldn't expect that error message if the export was done using VBA code instead of a macro. One disadvantage of macros is the lack of error handling. Jeanette Cunningham > Hi Jeanette: > [quoted text clipped - 99 lines] >> > >> > Shohoku79 Hi Jeanette: If Macro is going to keep on giving the format error problem, I am going to be using VBA code any way. So how do I go about Exporting a Query by VB? After doing some researching, I found TransferSpreadsheet.... If I trigger the event using the DoCmd.TransferSpreadsheet acExport within VB, it will require me to enter a file path for the filename argument, I wanted to give the user the ability to choose where he would want to put the generated file and the file name, but I don't know how to bring up the window to do so. Please let me know if there is a way. Thank you again for your help Shohoku Signature> I can't help you with the macro error message as I don't use macros. > I wouldn't expect that error message if the export was done using VBA code [quoted text clipped - 106 lines] > >> > > >> > Shohoku79 You can use the code at http://www.mvps.org/access/api/api0001.htm to let the user choose the folder and filename in the normal way. Paste this code (api0001) into a standard module in your database and save it with a name like modFile Use something like this in place of your macro (the code below will open the save file dialog, user enters a file name and where to save, clicks Ok and your query is exported) ---------------------------- Dim strFilter As String Dim strSaveFileName as string 'Display dialog strFilter = ahtAddFilterItem(strFilter, _ "Excel Files (*.XLS)", "*.XLS") strSaveFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ Filter:=strFilter, _ Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY) If Len(strSaveFileName) > 0 Then 'User entered a filename DoCmd.TransferSpreadsheet acExport, _ acSpreadsheetTypeExcel9, "qryExport", _ strSaveFileName Else 'User pressed Cancel 'Do nothing End If --------------------------------- Note: replace qryExport with the name of your table or query Jeanette Cunningham > Hi Jeanette: > [quoted text clipped - 149 lines] >> >> > >> >> > Shohoku79 Hi Jeanette: Thank you for the resources link. They were very helpful and I was able to implement those into my database without much problems. Here is another question though, after the Query has being exported to an Excel file (of the user's choosing of file name and location) and opening up the Excel file, it seems that all the headings as well as text data that were transferred over have an apostrophe preceeding the cell contents and it only shows up in the formula bar, not the sheet. e.g. 'Joe Smith (formula bar) and Joe Smith (actual sheet). This seem to be the way to indicate that this field should be treated as text by Excel and won't really affect the way the sheet looks, but is there a way to remove all the apostrophes in these cells as they are being exported by Access? Thank you, Shohoku Signature> You can use the code at http://www.mvps.org/access/api/api0001.htm to let > the user choose the folder and filename in the normal way. Paste this code [quoted text clipped - 182 lines] > >> >> > > >> >> > Shohoku79 This is a very hot topic with those who export data to excel. The apostrophe is added by Access during the export process. As you note it forces excel to treat numbers as text. If you change all the data to numbers in excel, you lose any leading zeros - which is not usually a good thing. If you change the error checking options in excel to turn off the checkbox for showing errror, you can hide the apostrophes in later versions of excel. The issue is an excel issue, if you ask on the excel discussion group, you can get some help with this. Jeanette Cunningham > Hi Jeanette: > [quoted text clipped - 232 lines] >> >> >> > >> >> >> > Shohoku79 Hi Jeanette: I'll post my question in those areas, thank you so much for all the help you have given me throughout this endeavor. Signature> This is a very hot topic with those who export data to excel. > The apostrophe is added by Access during the export process. [quoted text clipped - 244 lines] > >> >> >> > > >> >> >> > Shohoku79 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.