Solved

Exporting Access Query Results to .xlsx

Posted on 2010-09-22
17
1,394 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

708 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

14 Experts available now in Live!

Get 1:1 Help Now