• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

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
0
seamus9909
Asked:
seamus9909
1 Solution
 
NorieVBA ExpertCommented:
What type of query is QryEbill?
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
seamus9909Author Commented:
Having the existence of the file  myexcel.xlsx has no bearing on the error message.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Peter ColeCommented:
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.
0
 
seamus9909Author Commented:
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
0
 
Peter ColeCommented:
The error says contents of fields in 2 records were deleted.
Try exporting the query as a csv and the excel results as a csv and if not too long look for the differences. If a lot of rows long use something like winmerge.
0
 
seamus9909Author Commented:
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))
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now