Open/Save As and Close Excel (1 Viewer)

[2wistd]

My database consists of importing excel spreadsheets from another source. I have no control over the source but they export spreadsheets that don't seem to work right away. I have to open them up. Then i get

"The file you are trying to open, [Filepath].xls, is in a different format than expected by the file extension. Verify that the file is not corrupted and is from a trusted source before opining the file. Do you want to open the file now?" "Yes, No, Help"

Then I have to Save As on the file and save as Excel Workbook, which will make it an xlsx. I cannot simply change the file extention because it will then say the extension is not valid once you try to open it.

Can I have access have excel open/say yes, then save as?
If so how do I do that?

 

[ByteMyzer]

You can use the following code to convert the file to XLSX format before importing:

[COLOR="Navy"]Public Sub[/COLOR] SaveAsXLSX([COLOR="navy"]ByVal[/COLOR] sFilePath [COLOR="navy"]As String[/COLOR])

    [COLOR="navy"]Const[/COLOR] xlOpenXMLWorkbook = 51

    [COLOR="navy"]Dim[/COLOR] xlApp [COLOR="navy"]As Object
    Dim[/COLOR] xlBook [COLOR="navy"]As Object

    Set[/COLOR] xlApp = CreateObject("Excel.Application")

    [COLOR="navy"]Set[/COLOR] xlBook = xlApp.Workbooks.Open(sFilePath)

    sFilePath = Replace(sFilePath, ".xls", ".xlsx")
    sFilePath = Replace(sFilePath, ".xlsxx", ".xlsx")

    xlBook.SaveAs sFilePath, xlOpenXMLWorkbook
    xlBook.Saved = [COLOR="navy"]True[/COLOR]

    xlBook.Close
    [COLOR="navy"]Set[/COLOR] xlBook = [COLOR="navy"]Nothing[/COLOR]

    xlApp.Quit
    [COLOR="navy"]Set[/COLOR] xlApp = [COLOR="navy"]Nothing

End Sub[/COLOR]

 

[Old Man Devin]

It depends on how you are doing the export, but most export functions have an argument to choose the spreadsheet file type, usually via some cryptic format arguments like 'acSpreadsheetTypeExcel9'.

For example you can export via the line

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QueryName, filepath

which gives an export of a query in a 2010 excel format.

Also make sure the file name you are outputting it with has the right file extension on it, e.g. filepath = 'C:\Output.xlsx', as that could be causing the conflict.

 

[ByteMyzer]

Hello, Old Man Devin,

That is a good way to export a query as a spreadsheet. However, 2wistd is talking about importing spreadsheets, not exporting them.

 

[Old Man Devin]

Hello, Old Man Devin,

That is a good way to export a query as a spreadsheet. However, 2wistd is talking about importing spreadsheets, not exporting them.

Indeed, it was only wishful thinking that they could solve the import problem by solving the export problem, rather than working around it. Although the OP does imply they won't be able to change the export, so perhaps this is not possible.

 

[ByteMyzer]

The OP never said that there was an export problem, only that there was a problem with importing an Excel spreadsheet from another source. That other source may not necessarily be another MS Access application; it could be something else altogether.

There are a number of other applications, web-based or otherwise, that export a file with an extension of .XLS or .XLSX, but which are in fact tab-delimited text files. The solution I provided compensates for that by opening the file in an Excel.Application object instance and re-saving the file as a legitimate Excel spreadsheet file in .XLSX format.

 

[2wistd]

The original respond was perfect for my application!