Solved

Exporting Access Query Results to .xlsx

Posted on 2010-09-22
17
1,416 Views
Last Modified: 2013-11-05
Hello folks,

Im trying to export the results from an access query to .xlsx and im getting the following error, i have no idea what it means, any help would be greatly appreciated thanks...

The specified XML failed to validate against the schema. There is an error in the following line of the XML document: Whitespace is not allowed at this location
0
Comment
Question by:Eaddy Barnes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
17 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33740747
can you post the line of code you have? you might be using a wrong option

are you using docmd.outputquery?  eg  DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "c:\myfile.xlsx"

or transferdatabase?

DoCmd.TransferSpreadsheet acExport, , "myquery", "c:\myfile.xlsx"

if it is that, leave 2nd argument alone like I did above
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33740750
You might possibly be specifying acSpreadsheetTypeExcel12Xml or something like that in the 2nd arg, if you are using transferspreadsheet
0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33740848
sorry its access 2007
the query is being run on a table i imported into access

SELECT UCase(Left([dbl_listing].[brand],1)) & LCase(Mid([dbl_listing].[brand],2)) & " " & UCase(Left([dbl_listing].[headline],1)) & LCase(Mid([dbl_listing].[headline],2)) AS Name, 
dbl_listing.msrp AS Price, 
dbl_listing.status AS Status, 
dbl_listing.weight AS Weight, 
"<ul>" & (dbl_listing.bullet_points) & " </ul> <br /><br /><br /> <font color=#fe020e><b>Please note: All prices in US Dollars</b></font>" AS Description, 
dbl_listing.featured AS Featured, LCase(Replace(Replace(Replace(Replace(Replace(Replace(Replace(dbl_listing.packing_slip_desc," ","_"),"/",""),"-",""),"=",""),"+",""),"(",""),")","")) AS Product_URL, 
"UP/" & Replace(dbl_listing.main_image,".jpg","_90.jpg") AS Small_image_path, 
"UP/" & Replace(dbl_listing.addl_image,".jpg","_250.jpg") AS Large_image_path, 
dbl_listing.popular_buys AS Popular_Buys, dbl_listing.warehouse AS Warehouse
FROM dbl_listing
WHERE (((dbl_listing.category)="CAT5e & CAT5 Cables"))
AND dbl_listing.msrp >0
ORDER BY UCase(Left([dbl_listing].[brand],1)) & LCase(Mid([dbl_listing].[brand],2)) & " " & UCase(Left([dbl_listing].[headline],1)) & LCase(Mid([dbl_listing].[headline],2));

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33740895
the query looks fine but what do you do in order to try export to excel?
0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33740945
when the results come up i go to the ribbon under external data tab and simply click (Excel) Export to Excel Spreadsheet, choose export to location then hit finish then the error pops up..

Untitled-1.jpg
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33740994
I see. You are using access not code to do it.

I wonder if it gets confused because of this line

"<ul>" & (dbl_listing.bullet_points) & " </ul> <br /><br /><br /> <font color=#fe020e><b>Please note: All prices in US Dollars</b></font>" AS Description,

If u removed that then run the query, ensure it works then try export, does it work?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741016
Not sure, I tried that and it was fine.

Are you able to produce a sample of the outcome of the query? does it have any xml tags in there?
0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33741058
rockiroads, i would say its that line but i have exported other documents prior to this with that line in the query..

file attached...

101.csv
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741071
Not sure whats going on as I am unable to reproduce it. I do wonder if its perhaps due to data.

Try this vba solution just to check

hit alt-f11 to go into the vba window, add a new module then paste this in it


Public Sub DumpQueryToExcel()

    Dim sSql As String
    Dim objXL As Object
    Dim objWB As Object
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim sFile As String
    
    'Set name of file to open
    sFile = "c:\temp\myfile.XLSx"                   '<===  CHANGE THIS TO THE FILENAME YOU WANT THE RESULTS TO GO TO
    
    'Open a recordset for your query
    Set qdf = CurrentDb.QueryDefs("aq2")            '<===  CHANGE THIS aq2 TO YOUR QUERYNAME
    Set rs = qdf.OpenRecordset
    
    'Create a new excel document
    Set objXL = CreateObject("Excel.Application")
    
    'Create new workbook
    Set objWB = objXL.Workbooks.Add
    
    'Dump Data - Paste the values from your query starting from A1
    objWB.Sheets(1).Range("A1").CopyFromRecordset rs
    
    'To save current workbook
    objWB.SaveAs sFile
    
    'CLOSE DOWN
    objWB.Close
    objXL.Quit
    
    Set objWB = Nothing
    Set objXL = Nothing

    FollowHyperlink sFile
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:MAdS
ID: 33741078
This might be related with parsing of special characters at the time of the export. Look for character below in your results:
& " ' > <
For the sake of testing, see you can export some subquery that exclude records or fields containing those.

You might also try to export to XLS instead of XLSX.

Let me know how that goes...


0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33741114
can u try this test, wrap the html line with double quotes


SELECT UCase(Left([dbl_listing].[brand],1)) & LCase(Mid([dbl_listing].[brand],2)) & " " & UCase(Left([dbl_listing].[headline],1)) & LCase(Mid([dbl_listing].[headline],2)) AS Name, 
dbl_listing.msrp AS Price, 
dbl_listing.status AS Status, 
dbl_listing.weight AS Weight, 
chr$(34) & "<ul>" & (dbl_listing.bullet_points) & " </ul> <br /><br /><br /> <font color=#fe020e><b>Please note: All prices in US Dollars</b></font>" & chr$(34) AS Description, 
chr$(34) & dbl_listing.featured AS Featured, LCase(Replace(Replace(Replace(Replace(Replace(Replace(Replace(dbl_listing.packing_slip_desc," ","_"),"/",""),"-",""),"=",""),"+",""),"(",""),")","")) & chr$(34) AS Product_URL, 
"UP/" & Replace(dbl_listing.main_image,".jpg","_90.jpg") AS Small_image_path, 
"UP/" & Replace(dbl_listing.addl_image,".jpg","_250.jpg") AS Large_image_path, 
dbl_listing.popular_buys AS Popular_Buys, dbl_listing.warehouse AS Warehouse
FROM dbl_listing
WHERE (((dbl_listing.category)="CAT5e & CAT5 Cables"))
AND dbl_listing.msrp >0
ORDER BY UCase(Left([dbl_listing].[brand],1)) & LCase(Mid([dbl_listing].[brand],2)) & " " & UCase(Left([dbl_listing].[headline],1)) & LCase(Mid([dbl_listing].[headline],2));

Open in new window

0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33741168
@rockiroads your script worked but in the results I only got back 12 rows, was it suppose to give me only 12 rows? the total count of rows that should be there is 73 looks good though.

@MAds I took out the whole line   "<ul>" & (dbl_listing.bullet_points) & "</ul><br /><br /><br /><font color=#fe020e><b>Please note: All prices in US Dollars</b></font>" AS Description, and same error.
0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33741211
rockiroads that last query of yours worked in 1 go thanks.. what was the culprit exactly?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741214
which script?

when I said data issue it might be data not sql generating, it could be other data items also
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33741223
Oh, okay

What I did was wrap the columns in double quotes as a way of hiding the tags

so it is a value either in

dbl_listing.featured
or
bl_listing.bullet_points

you could try removing

chr$(34) &  .....   & chr$(34)

around one of them then try, if it works then you know which field might be the one that is causing the issue
0
 
LVL 5

Expert Comment

by:MAdS
ID: 33741357
My guess:

"CAT5e & CAT5 Cables"
------------^

Maybe you can try a "replace(dbl_listing.category,"&","and")
0
 
LVL 11

Author Comment

by:Eaddy Barnes
ID: 33741370
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question