JMoss
jmoss111 at bellsouth.net
Thu Jun 24 21:50:10 CDT 2004
One thing that you can do to write the field as text if you use Excel automation from Access is to use the Selection.NumberFormat = "@". And by using automation, you can also create the report header and formatting. jm -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Keith Williamson Sent: Thursday, June 24, 2004 8:51 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Access Export to Excel AND Quickbooks Hmmm. I think you are right....I had to do a work-around last night to move my data. I found the same thing......if I exported the data from the query, I COULD import into Excel ok. However, if I exported the report (based on the query), that is when I ran into problems. This is what I am trying to do; export from the report. I want to use the report to setup a template for importing into Quickbooks. I need the header area of the report for additional information in the template. If I have to export from the query....then I first need to import into Excel, and add the header info....prior to importing to Quickbooks. So, it is costing me an additional step, which I am trying to avoid. Maybe I can use a docmd.transfer ?? Or some other method of directly exporting the report with an .iif extension, that I can immediately import into Quickbooks. Of course, this would require the export process to maintain the code as the full text, and not truncate the zeros. See...overall...the problem is that Quickbooks is EXTREMELY useless for pricing updates. So I have my own database for manipulating pricing and adding new inventory items. So, I am trying to export these updates, and new items, to a file to import into Quickbooks. What a pain. Thanks, Keith E. Williamson Ashland Equipment, Inc phone # (410) 273-1856 fax # (410) 273-1859 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mitsules, Mark S. (Newport News) Sent: Thursday, June 24, 2004 9:21 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Access Export to Excel I know I've had this problem in the past...I think I may have even posted about it. But, at the moment I can't replicate the symptoms. I just tried a simple test using the same type of data you describe. I ran a query on this data, and chose "Tools/Office Links/Analyze It with Microsoft Excel". The result was a spreadsheet with the numbers shown as text with the little comment marker saying "number stored as text". I'm using Win2K / Office2K2. Perhaps I had this problem on an earlier setup like NT4.0 / Office 97. Mark -----Original Message----- From: Keith Williamson [mailto:kwilliam at ashlandnet.com] Sent: Thursday, June 24, 2004 8:11 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Access Export to Excel Well...I tried this, last night. I used the following in my report, with a text box: ="'"&[registerlisting] My report reflected the 4 digit codes (ex. 0045, 0321), with an ' in front of it (ie. '0045, '0321). When I pulled this into Excel....it also had an ' in front of all the codes. I know if I manually type an ' in the cell, Excel does not reflect the '......just keeps the entry as text. Apparently, when it is imported that way....it actually reflect the '. I'm sure there is something I'm just not tweaking correctly. Any other ideas out there?? Thanks again, Keith E. Williamson Ashland Equipment, Inc phone # (410) 273-1856 fax # (410) 273-1859 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson Sent: Wednesday, June 23, 2004 9:40 AM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Access Export to Excel Keith, In my experience, the only way to ensure the "number" remains "text" in Excel is to prepend with an apostrophe ( ' ). Also the first data element in the column determines the format for the entire column. Prior to your export you'll need to add the apostrophe. It won't show in Excel. HTH Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Keith Williamson Sent: Wednesday, June 23, 2004 7:35 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Access Export to Excel Hi all; I'm sure this is an easy one, but.....I have a report that I am trying to export to Excel. One of the fields is a text field (example: "0032", as text). When it exports to excel, it winds up truncating off the zeros, to become "32", instead. I really need this field to remain "0032". Any ideas? Thanks in advance. Keith E. Williamson Ashland Equipment, Inc phone # (410) 273-1856 fax # (410) 273-1859 -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com