Solved

Exporting Access Query Results to .xlsx

Posted on 2010-09-22
17
1,401 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:greeneel
  • 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:greeneel
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
 
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:greeneel
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:greeneel
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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:greeneel
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:greeneel
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:greeneel
ID: 33741370
thanks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now