Error 3190 on DoCMD.TransferSpreadsheet

[Kemidan2014]

So i have a small bit of code that I am using on a Command Button to export a query to Excel. it has been working until today where all i did to the code was move the Saveas and Close command.
according to google the error says its either related to too many fields (query has only 15 fields?) or exported file already exists but before today i was able to overwrite the existing file data. I checked and confirmed the file was not open when i clicked the button

Here is my code

Private Sub Qualitymetrics_Click()
'Declare stuff
Dim excelapp As Object
Dim wb As Object
Dim openpath As String
openpath = "O:\1_All Customers\Current Complaints\ComplaintMetricsQuery.xlsx" 'Set file path
Set excelapp = CreateObject("Excel.Application")
Set wb = excelapp.Workbooks.Open(openpath)
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ComplaintMetricsQuery", "O:\1_All Customers\Current Complaints\ComplaintMetricsQuery.xlsx", True
wb.SaveAs (openpath) '<---Originally after End if and before wb.close was only wb.save not save as.
wb.Close '<---Originally after End if AND disabled
If MsgBox("Metric's data is updated, Would you like to review the report?", vbYesNo) = vbYes Then
excelapp.Workbooks.Open (openpath)
excelapp.Visible = True
End If

End Sub

I changed where the save and close were because when i did use it it was almost like it was opening twice so i was trying to resolve the extra open excel instance.

 

[Kemidan2014]

I attempted the following since i had posted
Compacted and repaired data base, still get error
Saved a copy of my target file as an xls instead of xlsx then changed Acspreadsheet to 9 and 8 still same error

When i export my query manually it will export successfully with out issues. so I am not sure what i am missing.

 

[Micron]

Not something I have done for a long time but will suggest something that might not be correct (you'd have to test/research it):
AFAIK, when you set the wb object and use the .Open method you've opened a copy whether you can see it or not. When you transfer and use the same path as you did for .Open you're opening another instance of it. So If I'm recalling correctly, just transfer and don't open with respect to the same wb file. In fact I'm not seeing a need for variables or operations using the wb. Just transfer, then if you need to get into it, then open it?

 

[Kemidan2014]

So if i took your suggestion correctly i moved the setting of excel stuff to after the docmd line like this and it worked! also cleaned up the unnecessary code i just realized i had in there.

Private Sub Qualitymetrics_Click()
'Declare stuff
Dim excelapp As Object

Dim openpath As String
openpath = "O:\1_All Customers\Current Complaints\ComplaintMetricsQuery.xlsx" 'Set file path

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ComplaintMetricsQuery", "O:\1_All Customers\Current Complaints\ComplaintMetricsQuery.xlsx", True

Set excelapp = CreateObject("Excel.Application")


If MsgBox("Metric's data is updated, Would you like to review the report?", vbYesNo) = vbYes Then
 excelapp.Workbooks.Open (openpath)
 excelapp.Visible = True
End If

End Sub

I chose this method because i needed to NOT create a new excel file, i have tabs in the target book that run formulas for making graphs. i needed a way to overwrite the first of data each time.

question is if for whatever reason the data set from the next time i execute this macro is 1 or 2 rows smaller will that older data remain in the spreadsheet or will it go away. not sure if this type of transfer clears the spreadsheet contents or just overwrites only the range it outputs.

theoretically this list should only ever remain the same length or grow, not shrink the only time it would be opposite is between executions a complaint was cancelled.

 

[Micron]

Yes, you are now doing the transfer, then opening the workbook so the original problem should be solved.
AFAIK, it overwrites but you should test that theory on a wb copy because it may depend on which method you use - export or import. If it doesn't overwrite during an export, consider making the destination a dynamic named range and just delete the data in the range before the transfer. Find other sources for such a range if the one I linked to isn't satisfactory. I just picked the first in the search results to provide an example.