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
> Error Opening An Excel File Generated By Access Db, Office 2013    
 
   
SemiAuto40
post Apr 3 2020, 02:10 PM
Post#1



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


My Access database creates an Excel file through VBA. When I attempt to open that file I get a message:

"We found a problem with some content in 'qry_BatchSheets.xlsx'. Do you want to try to recover as much as we can?"

Once I click YES I get another popup "Excel was able to open the file by repairing or removing the unreadable content." Inside the white comment box is: "Repaired Records: Format from /Xl/styles.xml part (Styles)"

What is this all about!? How do I prevent this message???

Thanks.

Go to the top of the page
 
DanielPineault
post Apr 3 2020, 02:28 PM
Post#2


UtterAccess VIP
Posts: 7,338
Joined: 30-June 11



Without seeing your code it is very hard for us to help.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://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 3 2020, 02:43 PM
Post#3



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


Here it is.
CODE
Private Sub btn_Nav6_Click()

   On Error GoTo PROCESS_ERROR
    
   Dim MsgResult As Integer
        'CHECK THAT START DATE IS NOT EMPTY.
      If (Len(Nz(Me!frm_FormContainer.Form!startDate, "")) > 7) Then
         'CHECK THAT END DATE IS NOT EMPTY.
         If (Len(Nz(Me!frm_FormContainer.Form!endDate, "")) > 7) Then
            DoCmd.OpenQuery "qry_BatchSheets", acViewNormal, acReadOnly
            DoCmd.OutputTo acOutputQuery, "qry_BatchSheets", "ExcelWorkbook(*.xlsx)", "", False, "", , acExportQualityPrint
            DoCmd.Close acQuery, "qry_BatchSheets"
         Else
            MsgResult = MsgBox("End date must be a valid date.", vbExclamation, "Qc-Base Formulation Database message")
            Me!frm_FormContainer.Form!endDate.SetFocus
         End If
       Else
          MsgResult = MsgBox("Start date must be a valid date.", vbExclamation, "Qc-Base Formulation Database message")
          Me!frm_FormContainer.Form!startDate.SetFocus
       End If

EXIT_SUB:
  Err.Clear
  Exit Sub
    
PROCESS_ERROR:
  MsgBox (Err.Description)
  Resume EXIT_SUB

End Sub


Excel is generating the error upon open in Excel.

Thanks.
This post has been edited by SemiAuto40: Apr 3 2020, 02:49 PM
Go to the top of the page
 
DanielPineault
post Apr 3 2020, 03:02 PM
Post#4


UtterAccess VIP
Posts: 7,338
Joined: 30-June 11



You're missing spaces in your OutputFormat argument

"ExcelWorkbook(*.xlsx)" should be "Excel Workbook (*.xlsx)"
Or try acFormatXLSX

Also not sure why you pass "" for optional arguments, nor why you are specifying a OutputQuality for an Excel workbook.

So with that all in mind, try:
CODE
DoCmd.OutputTo acOutputQuery, "qry_BatchSheets", "Excel Workbook (*.xlsx)", , False

Or
CODE
DoCmd.OutputTo acOutputQuery, "qry_BatchSheets", acFormatXLSX, , False


Since AutoStart defaults to False (refer to https://docs.microsoft.com/en-us/office/vba...docmd.outputto) you can further simplify to
CODE
DoCmd.OutputTo acOutputQuery, "qry_BatchSheets", "Excel Workbook (*.xlsx)"

Or
CODE
DoCmd.OutputTo acOutputQuery, "qry_BatchSheets", acFormatXLSX

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://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 3 2020, 04:34 PM
Post#5



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


Same result with the new code suggestions. I'm wondering if it has anything to do with the format/design of the underlying data table not being understood or convertible by Excel.

The main underlying table has a 'Lookup' for Product_ID and a 'Lookup' for Operator. Would this cause it? I'm getting the data properly in the Dataview table query result. Does Excel understand 'Short text' as a data type. Would column labels cause a problem from the query result? I find the style sheet reference on the error odd.

Thank you for your time.
This post has been edited by SemiAuto40: Apr 3 2020, 04:46 PM
Go to the top of the page
 
DanielPineault
post Apr 3 2020, 06:27 PM
Post#6


UtterAccess VIP
Posts: 7,338
Joined: 30-June 11



What about

Trying acFormatXLS
Or
Using TransferSpreadsheet
Or
Using Excel automation

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://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
 
tina t
post Apr 4 2020, 12:49 AM
Post#7



Posts: 6,591
Joined: 11-November 10
From: SoCal, USA


and if all else fails, maybe

TransferText

would work. you should be able to open a text file in Excel and save as a .xlsx file.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 07:12 AM