Exporting Access Query Results to .xlsx

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
LVL 11
Eaddy BarnesITAsked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
You might possibly be specifying acSpreadsheetTypeExcel12Xml or something like that in the 2nd arg, if you are using transferspreadsheet
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Eaddy BarnesITAuthor Commented:
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
 
rockiroadsCommented:
the query looks fine but what do you do in order to try export to excel?
0
 
Eaddy BarnesITAuthor Commented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
Eaddy BarnesITAuthor Commented:
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
 
rockiroadsCommented:
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
 
MAdSCommented:
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
 
Eaddy BarnesITAuthor Commented:
@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
 
Eaddy BarnesITAuthor Commented:
rockiroads that last query of yours worked in 1 go thanks.. what was the culprit exactly?
0
 
rockiroadsCommented:
which script?

when I said data issue it might be data not sql generating, it could be other data items also
0
 
rockiroadsCommented:
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
 
MAdSCommented:
My guess:

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

Maybe you can try a "replace(dbl_listing.category,"&","and")
0
 
Eaddy BarnesITAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.