Michael Dean
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, acSpreadsheetTypeExcel12Xm l, "QryEbill", "C:\Documents\myexcel.xlsx ", True
FollowHyperlink "C:\Documents\myexcel.xlsx "
End Sub
QueryError.PNG
Private Sub Command60_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
FollowHyperlink "C:\Documents\myexcel.xlsx
End Sub
QueryError.PNG
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, acSpreadsheetTypeExcel12Xm l, "QryEbill", "C:\Documents\myexcel.xlsx ", True
FollowHyperlink "C:\Documents\myexcel.xlsx "
End Sub
Private Sub Command60_Click()
if dir("C:\Documents\myexcel.
kill "C:\Documents\myexcel.xlsx
end if
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
FollowHyperlink "C:\Documents\myexcel.xlsx
End Sub
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.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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))
Member #: Val(Left([Claim#],9))