Find a workbook and "overwrite it with the latest Excel format"
 

Hello All,

I conduct a daily download of several large queries from a remote Oracle
database. I save these queries as Excel Workbooks for import into an MS
Access database.

The problem I have is that the process is mostly automated with the
exception of the fact that I must go to each WB that I save and open them
and click "Save". This will bring up the dialogue box stating... "myfile.xls
is a Microsoft Excel 2.1 Worksheet. Do you want to overwrite it with the
latest Excel format?", at which point I click yes and it is updated to MS
Excel 2003. I have searched high and low but cannot seem to find a vba
example or instructions on how to write a macro that will

1st: Look in a directory/file path and identify all the files there so code
can be executed on each .xls in the dir.

2nd: Update each of the above identified files "with the latest Excel format"

Has anyone done this, seen code snippets or have more insight than this VBA
newbee to figure out a solution to this.

Any assistance would be greatly appreciated.


--
Thanks,

Patrick

Find a workbook and "overwrite it with the latest Excel format"
 

look at the FileSearch Object. It has the sample code you need

loop through the files found, then

open each
and then
do a


for i = 1 to .foundfiles(i)
workbooks.Open .FoundFilse(i)
Application.Displayalerts = False
activeworkbook.SaveAs Filename:=ThisWorkbook.FullName, _
FileFormat = xlWorkbookNormal
Activeworkbook.Close Savechanges:= false
Next i

--
Regards,
Tom Ogilvy



"PSKelligan" wrote in message
...
Hello All,

I conduct a daily download of several large queries from a remote Oracle
database. I save these queries as Excel Workbooks for import into an MS
Access database.

The problem I have is that the process is mostly automated with the
exception of the fact that I must go to each WB that I save and open them
and click "Save". This will bring up the dialogue box stating...
"myfile.xls
is a Microsoft Excel 2.1 Worksheet. Do you want to overwrite it with the
latest Excel format?", at which point I click yes and it is updated to MS
Excel 2003. I have searched high and low but cannot seem to find a vba
example or instructions on how to write a macro that will

1st: Look in a directory/file path and identify all the files there so
code
can be executed on each .xls in the dir.

2nd: Update each of the above identified files "with the latest Excel
format"

Has anyone done this, seen code snippets or have more insight than this
VBA
newbee to figure out a solution to this.

Any assistance would be greatly appreciated.


--
Thanks,

Patrick

Find a workbook and "overwrite it with the latest Excel format
 

Tom,

That was the problem. It is working perfectly now. Big thanks for your help.

Thanks,

Patrick

"Tom Ogilvy" wrote:

ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal

should be

ActiveWorkbook.SaveAs ActiveWorkbook.FullName, xlWorkbookNormal

ThisWorkbook refers to the workbook containing the code.

--
Regards,
Tom Ogilvy