Convert xlsx to xls in Acc 2003 VBA (1 Viewer)

Hi,
In my Acc 2003 App I need to import Excel files. I somtimes receive them as xlsx files, and would like to convert them to 2003 format (*.xls).
Here is a thread I've opend about this in the Excel forum.
I'd appreciate assistance from people in this forum too.

Here again is the link to the thread in Excel forum.

Thanks!

I'm not sure you can do this with A2003 since it has no knowledge of .xlsx files having been written four years before they came into being.

If you can read them with TransferSpreadsheet, you can rewrite them as a different version by changing one of the arguments in TransferSpreadsheet. If that doesn't work, you will need to use the automation code in your Excel post.

One trick I use when trying to come up with viable Excel code is to open Excel and turn on the macro recorder to record my steps. When I am done, I review the generated code and modify as necessary.

Hi Pat, and thank you for your reply! I do this manualy: open xlsx in my Excel 2003 (MS has published a compatibility pack), and save it as xls. The code I have does the work only in 2007 enviroment, running a mdb (2003) file. It seems to me the Excel Object can open and read the xslx file, but the 'SaveAs' has no parameter for xls2003 format. What I need is a parameter for this, or for the 'DefaultFormat' of the Excel Object.

It is possible to do this using Access 2003 as I do it myself. The method is to use Filesystem object to search for xlsx files in a selected folder and then use an Excel object to open the xlsx files and save in xls format.
If you want the full function posting I can do that.
David

Hi David, What you suggested is basicly what I do. If you could post the full function, that could be great! Thanks!

This function is passed the folder path from another function that uses Application.FileDialog(msoFileDialogFolderPicker) otherwise you can hard code the folder path

Function importExcel2007files(folderPath As String)
Dim ExcelApp As Object
Dim wkBook As Workbook
Dim newFileName, newPath As String
Dim fs As Object

Dim vaFileName As Variant
testExcel = "Excel.Application"
If IsAppRunning(testExcel) Then
Set ExcelApp = GetObject("Excel.Application")
Else
Set ExcelApp = CreateObject("Excel.Application")
End If
ExcelApp.Visible = True 'can comment this line out if you want it invisible

'this pass converts .xlsx files
With Application.FileSearch
.NewSearch
.LookIn = folderPath
.SearchSubFolders = False
.fileName = ".xlsx"

If .Execute > 0 Then
Set fs = CreateObject("Scripting.FileSystemObject")

For Each vaFileName In .FoundFiles
If vaFileName Like "*.xlsx" Then
newFileName = folderPath & "\" & someNewFileName & ".xls"
Set wkBook = Workbooks.Open(vaFileName)
wkBook.SaveAs newFileName, FileFormat:=-4143

wkBook.Close True



End If
Next

End If

End With
Set fs = Nothing



ExcelApp.Quit

Set ExcelApp = Nothing


End Function

Function IsAppRunning(ByVal sAppName) As Boolean
Dim oApp As Object
On Error Resume Next
Set oApp = GetObject(, sAppName)
If Not oApp Is Nothing Then
oApp.Quit
Set oApp = Nothing
IsAppRunning = False
End If
End Function

I hope this is ok as I've deleted some lines of code that creates a new file name before saving

David

Wow! this FileFormat:=-4143 is what I was looking for. I've actualy tryed it the other day, but now it works. Thank You!

Just an FYI: Was looking into that for a different flavor of the an issue.
These two links include code examples and offer the different numbers uses. There is also a statement about using the numbers rather than the defined constants for compatibility reasons.

These two links are very close to the same one written in Excel 2007 and one for Excel 2010:
http://www.rondebruin.nl/saveas.htm
http://blogs.office.com/b/microsoft.../use-the-vba-saveas-method-in-excel-2007.aspx

What was of interest is code to open Workbook with Macro Enabled:
These are the main file formats in Excel 2007:

51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, .xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro's, .xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, .xls)
AND
These are the main file formats in Excel 2007-2010:

51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)


Note: I always use the FileFormat numbers instead of the defined constants
in my code so that it will compile OK when I copy the code into an Excel
97-2003 workbook. (For example, Excel 97-2003 won't know what the
xlOpenXMLWorkbookMacroEnabled constant is.)