Convert Excel file in older format

[greaseman]

I have a user who continually creates and saves an Excel file in Excel 2.1 format, although she has Excel 97 on her machine. Although I've advised her to save in Excel 97 format, she "forgets" most of the time. What I'm looking for is any kind of VBA code snippet that will simply open her Excel file and save it in Excel 97 format, from within an Access 97 code module.

I wasn't able to find anything in the various forums, which is why I'm posting.

Thank you to all who reply!!

 

[aphelps]

The following will export a form to Excel 97, hope this helps. I don't know how to get the thing to open, though I'm sure there's a similar DoCmd statement that will allow you to open an older Excel file. Let me know if you can't find it and I'll see if I can dig something else up--with the caveat that I am no code guru!

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "ObjectNameToExport", "FileNameAsExported.xls", True

 

[greaseman]

Thanks for your response..... that was similar to what I had in my code, but it looks like Access 97 does not support "antique" formats with the Transfer spreadsheet stuff. I got error message about "the expected format wasn't there....".

So - what I did was the following piece of code - which works pretty well. My only problem now is checking for the existence of the output file from a previous run and deleting it if it's out there. If I don't do this, the process stops and asks if I want to overwrite. That's fine, except we want to have this thing run automatically. Any ideas? Thoughts? Suggestions? Comments? Thanks again for replying!

Dim db As Database
Dim objXL As Excel.Application
Dim objWkb As Workbook
Dim objSht As Worksheet

Const conWKB_NAME = "\CETUS\FS\HR DATA\HR DATA.xls"
Const conWKB_FILE = "\CETUS\FS\HR DATA\HR DATABACKUP.xls, FileFormat:=xlExcel9795, Password:='', WriteResPassword:='', ReadOnlyRecommended:=False, CreateBackup:=False"

'**********************************

On Error GoTo ImportEmployeeData_Err
Call TraceMyCode("ImportEmployeeData", "")

'*****************************************
Set objXL = New Excel.Application

If bFileFound("\\Cetus\Fs\HR DATA\HR Backup.xls") Then
Kill "\\Cetus\Fs\HR DATA\HR Backup.xls"
End If
With objXL
.Visible = False
Workbooks.Open FileName:="\\Cetus\Fs\HR DATA\HR DATA.xls"
ActiveWorkbook.SaveAs FileName:="\\Cetus\Fs\HR DATA\HR Backup.xls", _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks.Close
End With

 

[Late4Supper]

In EXCEL XP you can set default Save As format which may help going forward (Excel 2.1 is an option).

Right off the Help screen

Change the default file format for saving workbooks
By default, workbooks are saved in the current Microsoft Excel format. If you work with others who use earlier versions of Microsoft Excel or other spreadsheet programs, you can use another format as the default for saving workbooks.

On the Tools menu, click Options, and then click the Transition tab.
In the Save Excel files as box, click the file format you want.
Notes

After you set the default file format, all new workbooks are saved in the selected file format unless you specify a different format in the Save As dialog box when you save a workbook.

Some features and formatting might not be available if you save the workbook in the file format of a previous version of Microsoft Excel or another spreadsheet program.

 

[greaseman]

Thanks for responding! Unfortunately, we're using Excel 97, not XP. Plus we're trying to automate the whole thing from withing Access, having Access 97 call out to Excel, and so on.

We're almost there, though........

Thanks for your idea - it's appreciated

 

[Late4Supper]

I found a machine with Excel 97 and you can do the same thing as XP for Save As format default, but like you said, you want to automate from within ACCESS 97 so it's just useless info.

 

[greaseman]

Did you take a look at my earlier post, where I showed the code we're using? It's actually working pretty well, and outside of a few permissions issues, will probably be the road we'll follow.

Thanks again!