VBA to automatically test for file, update, overwrite

[Brent E]

Good Morning,

I am looking for the VBA code to:

A. Test if an Excel file is already open, e.g. D:\Data\Sites.xls. If yes,
activate current file. If no, open file.

[Do whatever...]

B. Then automatically save the file and overwrite prompt as 'Yes' for
existing file on close? So the module does not pause waiting for a yes prompt
from the user to overwrite.

Thanks

 

[Dave Peterson]

What does activate the current file mean--the sites.xls workbook?

Did you want to save the sites.xls and close it? I'm not sure why opening,
saving, closing would accomplish.

I'm confused about what current and existing mean in your question.

But maybe this will get you started.


Dim TestWkbk as workbook
set testwkbk = nothing
on error resume next
set testwkbk = workbooks("sites.xls")
on error goto 0

if testwkbk is nothing then
set testwkbk = workbooks.open(filename:="d:\data\sites.xls")
end if

testwkbk.close savechanges:=true
'or to close the workbook that holds the code
thisworkbook.close savechanges:=true

Good Morning,

I am looking for the VBA code to:

A. Test if an Excel file is already open, e.g. D:\Data\Sites.xls. If yes,
activate current file. If no, open file.

[Do whatever...]

B. Then automatically save the file and overwrite prompt as 'Yes' for
existing file on close? So the module does not pause waiting for a yes prompt
from the user to overwrite.

Thanks

 

[Brent E]

Thanks Dave,

That should be what I need. I am basically going to use an Access module to
export data and then call an excel macro to open several Excel files, perform
some formatting updates, and then save the files as html files and close
them. I have the rest pretty well completed, I just needed the code to check
to see if one of the files was already open, if so use the open file and then
save the files on exit without prompting the user for overwrite permission.

Thanks Much.

What does activate the current file mean--the sites.xls workbook?

Did you want to save the sites.xls and close it? I'm not sure why opening,
saving, closing would accomplish.

I'm confused about what current and existing mean in your question.

But maybe this will get you started.


Dim TestWkbk as workbook
set testwkbk = nothing
on error resume next
set testwkbk = workbooks("sites.xls")
on error goto 0

if testwkbk is nothing then
set testwkbk = workbooks.open(filename:="d:\data\sites.xls")
end if

testwkbk.close savechanges:=true
'or to close the workbook that holds the code
thisworkbook.close savechanges:=true

Good Morning,

I am looking for the VBA code to:

A. Test if an Excel file is already open, e.g. D:\Data\Sites.xls. If yes,
activate current file. If no, open file.

[Do whatever...]

B. Then automatically save the file and overwrite prompt as 'Yes' for
existing file on close? So the module does not pause waiting for a yes prompt
from the user to overwrite.

Thanks

 

[Dave Peterson]

Good luck!

Thanks Dave,

That should be what I need. I am basically going to use an Access module to
export data and then call an excel macro to open several Excel files, perform
some formatting updates, and then save the files as html files and close
them. I have the rest pretty well completed, I just needed the code to check
to see if one of the files was already open, if so use the open file and then
save the files on exit without prompting the user for overwrite permission.

Thanks Much.

What does activate the current file mean--the sites.xls workbook?

Did you want to save the sites.xls and close it? I'm not sure why opening,
saving, closing would accomplish.

I'm confused about what current and existing mean in your question.

But maybe this will get you started.


Dim TestWkbk as workbook
set testwkbk = nothing
on error resume next
set testwkbk = workbooks("sites.xls")
on error goto 0

if testwkbk is nothing then
set testwkbk = workbooks.open(filename:="d:\data\sites.xls")
end if

testwkbk.close savechanges:=true
'or to close the workbook that holds the code
thisworkbook.close savechanges:=true

Good Morning,

I am looking for the VBA code to:

A. Test if an Excel file is already open, e.g. D:\Data\Sites.xls. If yes,
activate current file. If no, open file.

[Do whatever...]

B. Then automatically save the file and overwrite prompt as 'Yes' for
existing file on close? So the module does not pause waiting for a yes prompt
from the user to overwrite.

Thanks