 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2005DoCmd.TransferSpreadsheet
I'm using the DoCmd.TransferSpreadsheet command to export to a spreadsheed the results of a query. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Qry_EmpHrs_Total", "Summary_Project_Hours", True But I get an error msg, "Too many fields defined." The query is dymanically build via vba. The query only produces about 20 columns when I run it manually. What's going on and how to fix? Thanks.  SignatureBill Bill: Have you tried to Debug.Print the dynamically built query to check that it is in the expected format and with the expected number of columns? Another suggestion is to post the VBA code that builds the query dynamically, as it appears the query is the most likely source of the issue. SignatureDavid Lloyd MCSD .NET http://LemingtonConsulting.com This response is supplied "as is" without any representations or warranties. I'm using the DoCmd.TransferSpreadsheet command to export to a spreadsheed the results of a query. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Qry_EmpHrs_Total", "Summary_Project_Hours", True But I get an error msg, "Too many fields defined." The query is dymanically build via vba. The query only produces about 20 columns when I run it manually. What's going on and how to fix? Thanks. SignatureBill The query appears fine. It is the result of a crosstab query. The number of columns and rows varies. I know it works because I have an option to print to a report using the same query. Printing (using a report), and running the query in access works fine, export doesn't when there's a "higher" column count. For example, if I run the query using data that creates 7 columns and 12 rows, that works. Change the parameters so that the query produces 20 columns and 10 rows, and the export fails. This is what the crosstab looks like: TRANSFORM Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS SumOfSumOfEmpHrs SELECT Qry_EmpHrs_Total_By_Project_Task.Task, Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS TotTaskHrs FROM Qry_EmpHrs_Total_By_Project_Task WHERE (((Qry_EmpHrs_Total_By_Project_Task.Project_Label) = 'CSMES')) GROUP BY Qry_EmpHrs_Total_By_Project_Task.Task PIVOT Qry_EmpHrs_Total_By_Project_Task.Initials; The exact error msg is: Run-time error '3190:' Too many fields defined. Also, another question. I want to use a string variable for the filename in the transferspreadsheet command, but I get an error when I try. dim fName as string fName= "long file name" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Qry_EmpHrs_Total", fName, True How can I get that to work? > Bill: > > Have you tried to Debug.Print the dynamically built query to check that it > is in the expected format and with the expected number of columns? Another > suggestion is to post the VBA code that builds the query dynamically, as it > appears the query is the most likely source of the issue. Bill: I was able to export a crosstab with 69 columns and 397 rows without error. I believe 255 columns is the maximum for an Access query. Whatever issue you are having, it appears specific to your implementation. Regarding the second question, there should be no reason why using a string variable for the file name should cause an issue. Again, I tested this and did not receive an error. If you can give me more specifics regarding your code, I will try to help you pinpoint the issue. SignatureDavid Lloyd MCSD .NET http://LemingtonConsulting.com This response is supplied "as is" without any representations or warranties. The query appears fine. It is the result of a crosstab query. The number of columns and rows varies. I know it works because I have an option to print to a report using the same query. Printing (using a report), and running the query in access works fine, export doesn't when there's a "higher" column count. For example, if I run the query using data that creates 7 columns and 12 rows, that works. Change the parameters so that the query produces 20 columns and 10 rows, and the export fails. This is what the crosstab looks like: TRANSFORM Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS SumOfSumOfEmpHrs SELECT Qry_EmpHrs_Total_By_Project_Task.Task, Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS TotTaskHrs FROM Qry_EmpHrs_Total_By_Project_Task WHERE (((Qry_EmpHrs_Total_By_Project_Task.Project_Label) = 'CSMES')) GROUP BY Qry_EmpHrs_Total_By_Project_Task.Task PIVOT Qry_EmpHrs_Total_By_Project_Task.Initials; The exact error msg is: Run-time error '3190:' Too many fields defined. Also, another question. I want to use a string variable for the filename in the transferspreadsheet command, but I get an error when I try. dim fName as string fName= "long file name" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Qry_EmpHrs_Total", fName, True How can I get that to work? > Bill: > > Have you tried to Debug.Print the dynamically built query to check that it > is in the expected format and with the expected number of columns? Another > suggestion is to post the VBA code that builds the query dynamically, as it > appears the query is the most likely source of the issue. Bill, 1) I notice you're exporting to the antique Excel 3 format, which dates back to the days of Windows 3.0 and has limitations not present in the current Excel 8/9/10 format. Is this deliberate, to accommodate the requirements of some other antique software you're using? If not, change acSpreadsheetTypeExcel3 to acSpreadsheetTypeExcel8. 2) Are you exporting to a worksheet that already exists and has data on it? If so, you may hit this or a similar error if there are more fields in your query than in the existing data on the worksheet. >I'm using the DoCmd.TransferSpreadsheet command to export to a spreadsheed >the results of a query. [quoted text clipped - 10 lines] > >Thanks. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. Well, that was interesting. I changed to "8" and it exported a blank spreadsheet! I'm using access 2000, if that matters. If the spreadsheet already exists, it gets deleted by the export command (so it seems) > Bill, > [quoted text clipped - 27 lines] > > Please respond in the newgroup and not by email. Ah, that seems to help, once I found just where it put the @&$* file! > Bill, > [quoted text clipped - 27 lines] > > Please respond in the newgroup and not by email. I am having this exact problem. Since I am already exporting to acSpreadsheetTypeExcel9 I can't see what Bill did to solve the problem. I am saving over the same spreadsheet each time since there is excel formatting that the user requires (e.g. mmm-yy date) and the resulting spreadsheet is only used to export to a third party software. If I could figure out the code to delete the spreadsheet before exporting again that might be a solution but I don't know how to do that. Can anyone help? |
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.