Excel 95

[Guest]

Hi

I have a problem with an Excel file that I want to auto update against data
in other Excel files - unfortunately the source files are created by Access
and so are Excel 95 format. The update works fine if the source files are
open but gives an error message if not. If I re-save the source file as Excel
2000 it works without the error. There will be circa 240 source files and
when updated by Access will be over written so it is not feasible to manually
convert the files - there are approx 350 of them!

Any ideas?

Thank you

 

[Bob Phillips]

Access has later file formats. Could you not change that job to output in a
later version?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi

Thank you for the advice, we can do this, but this doesnt help with the
other files we have already saved in the older format, these need to be
updated but will be too time consuming to do one at a time.

Thank you

 

[Bob Phillips]

So a once-off VBA to do it?

Dim aryFiles
Dim oFSO

Sub LoopFolders()
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim oWB As Workbook
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Set oWB = Workbooks.Open(Filename:=file.Path)
Application.DisplayAlerts = False
If oWB.FileFormat <> xlWorkbookNormal Then
oWB.SaveAs Filename:=file.Path, FileFormat:=xlWorkbookNormal
End If
oWB.Close savechanges:=False
Application.DisplayAlerts = truw
End If
Next file

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you

Incidentally, do you know how to export from access to excel as 2000 not as
95?

 

[Bob Phillips]

You get a dropdown of file types, you have to select 97-2000 rather than 95.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thank you for your help, Youve been a star!

You get a dropdown of file types, you have to select 97-2000 rather than 95.

--

HTH

RP
(remove nothere from the email address if mailing direct)