Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • 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
 
NorieCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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