Link to home
Start Free TrialLog in
Avatar of Michael Dean
Michael DeanFlag for United States of America

asked on

Query output to Excel producing error

I have a query that when I run it producing the attached error.   I am not sure what it is saying, I am exporting the results of the query to a Excel Spreadsheet with the following:

Private Sub Command60_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QryEbill", "C:\Documents\myexcel.xlsx", True
 
 FollowHyperlink "C:\Documents\myexcel.xlsx"

 End Sub
QueryError.PNG
Avatar of Norie
Norie

What type of query is QryEbill?
try deleting the file first if it exists

Private Sub Command60_Click()

if dir("C:\Documents\myexcel.xlsx") <> "" then
     kill "C:\Documents\myexcel.xlsx"
end if


 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QryEbill", "C:\Documents\myexcel.xlsx", True
   
  FollowHyperlink "C:\Documents\myexcel.xlsx"

  End Sub
Avatar of Michael Dean

ASKER

Having the existence of the file  myexcel.xlsx has no bearing on the error message.
I think the transfer determines the type of data in afield from the first few rows. Does QryEbill have the same type of data in all rows.  Are the first few rows numeric in one field then have Alpha characters later. Or date problems.
so the data looks ok. I compared the qry results in the designer to the excel spreadsheet and and the number of rows match I don't see anything wrong
ASKER CERTIFIED SOLUTION
Avatar of Peter Cole
Peter Cole
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have a query that results in a field being created that if there are leading 0's will drop them from the results.  I would like the entire 9 digits to appear even when if there are 0's

Member #: Val(Left([Claim#],9))