[AccessD] Access Export to Excel AND Quickbooks

Keith Williamson kwilliam at ashlandnet.com
Thu Jun 24 08:50:53 CDT 2004


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




More information about the AccessD mailing list