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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
out great, however I am having a little trouble working out the version
update part. My code follows:

'***************************************
Option Explicit
'***************************************
Public Sub FSearch(fsPath As String, f_ext As String)
Dim fs
Dim i As Integer
Set fs = Application.FileSearch
With fs
.LookIn = fsPath
.Filename = f_ext
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Application.DisplayAlerts = False
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
'***************************************
'
'***************************************
Sub CallFSearch()
Call FSearch("C:\MyFilePath", "*.xls")
End Sub
'***************************************
I am recieving the following error:

Run-time error '1004':
You cannot save this workbook with the same name as another open workbook or
add-in. Choose a different name, or close the other workbook or add-in before
saving.

I am calling proceedure from the code window in my hidden PERSONAL.xls Macro
workbook. I do not know if this matters.

Any Idea what is wrong with my code?

Thanks,
Patrick
 
#########Repost with missing lines added##########
##########################################

Hi Tom,

Thanks for you response. The refrence to the FileSearch Object worked out
great, however I am having a little trouble working out the version update
part. My code follows:

'***************************************
Option Explicit
'***************************************
Public Sub FSearch(fsPath As String, f_ext As String)
Dim fs
Dim i As Integer
Set fs = Application.FileSearch
With fs
.LookIn = fsPath
.Filename = f_ext
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Application.DisplayAlerts = False
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
'***************************************
'
'***************************************
Sub CallFSearch()
Call FSearch("C:\ERDLogTrack\Feeders", "*.xls")
End Sub
'***************************************
With the option explicit, the (I assume they are keywords) keywords
"Filename" and "FileFormat" are giving me an 'Variable not defined" error. I
added them to my declarations and I get the following error.

Run-time error '1004':
Method 'SaveAs' of Object '_Workbook' failed

Any Idea what is wrong with my code?

Thanks,
Patrick
 
I copied your code from the email and pasted it into a general module,
created a MyFilePath directory in the C drive and copied three xls files to
it.

Ran your code and had no problems with it.

Is the workbook that contains the code also located in that directory - if
so, maybe that is the problem.
 
I don't see fileformat in you code anywhere although it code be used in the
argument of FileSaveas. In that context you would write

ActiveWorkbook.SaveAs ThisWorkbook FileName:=FullName, _
FileFormat:= xlWorkbookNormal

You would not declare them as variables. Note that assignments for named
arguments are made with a colon and equal sign :=
 
Hi Tom,

The code is contained in PERSONAL.xls, my macro workbook located in
"XLSStart". When the code, exacly as it appears below, runs, it opens the
first wb in the directory and gives me the following error:
******************************************
Run-Time error '1004':

You cannot save this woorkbook with the same name as another open
workbook or add-in. Choose a different name or close the other
workbook or add-in before saving.
******************************************
When I select "debug", the following line is highlighted as the offending
line of code:

ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal

Code used follows:

Public Sub FSearch9(fsPath As String, f_ext As String)
Dim fs
Dim i As Integer
Set fs = Application.FileSearch
With fs
.LookIn = fsPath
.Filename = f_ext
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Application.DisplayAlerts = False
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
'***************************************
'
'***************************************
Sub CallFSearch9()
Call FSearch9("C:\MyFilePath", "*.xls")
End Sub
'***************************************

Could my problem be the open hiden macro book in the background or possibly
the fact that the proceedure has the book I want to save to the newer format
open already?

Thanks for your help,

Patrick
 
ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal

should be

ActiveWorkbook.SaveAs ActiveWorkbook.FullName, xlWorkbookNormal

ThisWorkbook refers to the workbook containing the code.

--
Regards,
Tom Ogilvy

PSKelligan said:
Hi Tom,

The code is contained in PERSONAL.xls, my macro workbook located in
"XLSStart". When the code, exacly as it appears below, runs, it opens the
first wb in the directory and gives me the following error:
******************************************
Run-Time error '1004':

You cannot save this woorkbook with the same name as another open
workbook or add-in. Choose a different name or close the other
workbook or add-in before saving.
******************************************
When I select "debug", the following line is highlighted as the offending
line of code:

ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal

Code used follows:

Public Sub FSearch9(fsPath As String, f_ext As String)
Dim fs
Dim i As Integer
Set fs = Application.FileSearch
With fs
.LookIn = fsPath
.Filename = f_ext
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
Application.DisplayAlerts = False
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.SaveAs ThisWorkbook.FullName, xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
'***************************************
'
'***************************************
Sub CallFSearch9()
Call FSearch9("C:\MyFilePath", "*.xls")
End Sub
'***************************************

Could my problem be the open hiden macro book in the background or possibly
the fact that the proceedure has the book I want to save to the newer format
open already?

Thanks for your help,

Patrick
Click to expand...
 
Tom,

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

Thanks,

Patrick
 
Back
Top