UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export To Excel From Access, Access 2016    
 
   
SemiAuto40
post Apr 19 2019, 10:58 AM
Post#1



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


I have been beating on this to no avail. I export to Excel from an Access form and when I try to open the Excel file I get: "We found a problem with some content in "TheNewSpreadsheetName.xlsx". Do you want to try to recover as much as we can? If you trust the source of this workboook click Yes. Then, another popup that Excel was able to open the file by repairing or removing the unreadable content. Repaired records: Format from /xl/styles.xml part (Styles). There is then a link to an XML recovery log.

What the!? I have not been seeing this before or not noticed it until this current project.

Thanks in advance.
Go to the top of the page
 
GroverParkGeorge
post Apr 19 2019, 11:51 AM
Post#2


UA Admin
Posts: 35,316
Joined: 20-June 02
From: Newcastle, WA


Sometimes it can be hard to diagnose problems like this without a "hands on" chance to track the actual process and see what the environment is where it occurs. Can you share any of the elements for review?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SemiAuto40
post Apr 19 2019, 01:03 PM
Post#3



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


The query behind the data is caught from an open form where dates are input.
CODE
SELECT Batches.ID, Batches.product, Batches.batchNumber, Batches.operator, Batches.batchStartDate, Batches.actualClayCharge, Batches.techCharge, Batches.ttNum, Batches.ttBill, Batches.Comments, Batches.rcNum
FROM Batches
WHERE (((Batches.batchStartDate) Between [Forms]![Batch Sheet Query]![startDate] And [Forms]![Batch Sheet Query]![endDate]));


The code behind the button on the form with the dates input:
CODE
Private Sub cmd_ExportBatchSheetToExcel_Click()
   DoCmd.OpenQuery "BatchSheetsToRetain", acViewNormal, acReadOnly
   DoCmd.OutputTo acOutputQuery, "BatchSheetsToRetain", acFormatXLSX, "", False, "", , acExportQualityPrint
   DoCmd.Close acQuery, "BatchSheetsToRetain"
End Sub


PS - If I manually export through the ribbon and select the keep formatting option this problem happens. If I export the same data without keeping the formatting option then the data transfers and opens without incident. The formatting is important since the row one is the header information gotten from the table as Access does.
This post has been edited by SemiAuto40: Apr 19 2019, 01:15 PM
Go to the top of the page
 
River59
post Apr 19 2019, 01:30 PM
Post#4



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


Not sure about your format 'acFormatXLS'.

Try using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml,"BatchSheetsToRetain", "BatchSheetsToRetain" & ".xlsx", False, "", , acExportQualityPrint


--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
SemiAuto40
post Apr 19 2019, 02:43 PM
Post#5



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


Wrong number of arguments or invalid property on the DoCmd.TransferSpreadsheet . I couldn't help but notice that only Excel 2010 is as far as the spreadsheet type goes.
Go to the top of the page
 
DanielPineault
post Apr 19 2019, 02:53 PM
Post#6


UtterAccess VIP
Posts: 6,717
Joined: 30-June 11



Try http://www.devhut.net/2017/03/15/ms-access...rdset-to-excel/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Apr 19 2019, 02:58 PM
Post#7


UtterAccess VIP
Posts: 6,717
Joined: 30-June 11



Nothing wrong with using acFormatXLSX (Const acFormatXLSX = "Microsoft Excel Workbook (*.xlsx)") see http://www.devhut.net/2014/08/07/docmd-out...el-2007-format/
You don't need to use "" if you don't want to specify optional input variables.
CODE
DoCmd.OutputTo acOutputQuery, "BatchSheetsToRetain", acFormatXLSX, , False, , , acExportQualityPrint

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
SemiAuto40
post Apr 19 2019, 03:43 PM
Post#8



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


The same "found a problem with some content" Excel error is there with Daniel's last response. It is Excel generating the error and then going about the "repair". I've checked and the only thing that seems to be happening is the header row being taken from that generated by Access. The header row is taken from the datasheet that Access generates in response to the query.

Is there some template that Access uses to do the export? The header row is taken from the table most certainly.
This post has been edited by SemiAuto40: Apr 19 2019, 03:47 PM
Go to the top of the page
 
DanielPineault
post Apr 19 2019, 06:20 PM
Post#9


UtterAccess VIP
Posts: 6,717
Joined: 30-June 11



Definitely sounds like an Excel issue. You could check to see if your personal.xlb or other base files (ExcelXX.xlb, ...) are corrupt. An Office Repair sounds good.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
SemiAuto40
post Apr 22 2019, 09:55 AM
Post#10



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


I have narrowed down the Access export to Excel problem. !For some reason if I take out the 4 date or time fields from the table the table data exports properly so that Excel opens it without complaint!

Any ideas why the same version of Excel and Access would do such a thing?? How could I now use date and time fields to include in my export?

Thanks.
Go to the top of the page
 
GroverParkGeorge
post Apr 22 2019, 10:18 AM
Post#11


UA Admin
Posts: 35,316
Joined: 20-June 02
From: Newcastle, WA


Perhaps the problem is that your "dates" being interpreted by Excel as strings.

Keep in mind that any time you use the Format() function to apply a particular DISPLAY format to a date, it converts that value to a string. That could be part of the problem.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
SemiAuto40
post Apr 22 2019, 10:29 AM
Post#12



Posts: 695
Joined: 3-April 12
From: L.A. (lower Alabama)


After spending waaaay too much time on this, (when I don't think it should be necessary Microsoft) - I have narrowed it down to one factor. When I remove all input mask from the data table - it exports without an Excel error.

So now.... what to do about the input masks in the date and time fields???

BTW, the masks were as Access offered them. "Medium time" for time. "Short date" for date.
This post has been edited by SemiAuto40: Apr 22 2019, 10:39 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 05:00 PM