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

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)


"Michelle" wrote in message
...
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

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" wrote:

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)


"Michelle" wrote in message
...
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

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)


"Michelle" wrote in message
...
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" wrote:

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)


"Michelle" wrote in message
...
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

Thank you

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

"Bob Phillips" wrote:

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)


"Michelle" wrote in message
...
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" wrote:

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)


"Michelle" wrote in message
...
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

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)


"Michelle" wrote in message
...
Thank you

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

"Bob Phillips" wrote:

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)


"Michelle" wrote in message
...
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" wrote:

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)


"Michelle" wrote in message
...
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

Thank you for your help, Youve been a star!

"Bob Phillips" wrote:

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)


"Michelle" wrote in message
...
Thank you

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

"Bob Phillips" wrote:

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)


"Michelle" wrote in message
...
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" wrote:

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)


"Michelle" wrote in message
...
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