How to open a new workbook every time the macro executes

**Aswathy** · 10-22-2008, 04:35 AM

Hi All,

I have a code where I am opening a new file every time the macro executes. How can I write a check in VBA to over write the file if it already exists.

Please Login or Register  to view this content.

UniqueIssueTypeList(temp_i) gives me the file name for e.g. File1

Cheers,
Aswathy

**Stuie** · 10-22-2008, 05:03 AM

Hi Aswathy,

What exactly do you mean by overwrite?? do you mean to save it and overwrite origanal file or close the file if its open and then open this one??

**royUK** · 10-22-2008, 05:20 AM

You need to create a template workbook, see

http://www.excel-it.com/excel_templates.htm

**Aswathy** · 10-22-2008, 06:10 AM

Hi Roy,

Thanks for the heads up. What I meant was the file may not be open all the time I try to write data into it. If the file exists in that location, then writing of data, shouldn't be appending to the original one but rewriting the whole file.

I hope I made it clear this time

Please Login or Register  to view this content.

My approach to this is the code written above, I want to know like in many other languages, does VBA support opening of a file in over-write mode.

Cheers,
Aswathy

**royUK** · 10-22-2008, 06:12 AM

You need a Function to check if the file exists, see my posted code here

http://www.excelforum.com/excel-prog...ts-method.html

**Aswathy** · 10-22-2008, 06:23 AM

Hi Roy,

I already had a look at this piece of code

I was already doing the same thing, the else part if the fileExists was my concern.

Cheers,
Aswathy

**royUK** · 10-22-2008, 06:35 AM

Do you mean

Please Login or Register  to view this content.

**Aswathy** · 10-22-2008, 06:39 AM

Hi Roy,

The saveCopyAs will create another copy of the existing file, isn't it. The thing is I am creating let say 4 files like this, so every time when the user runs the macro it will create a new one with the file name suffixed with the current date. I feel this is not needed since the older versions of the file data will be obsolete. That is why I am trying to reuse the same file.

Cheers,
Aswathy

How to open a new workbook every time the macro executes

LinkBack

Thread Tools

Rate This Thread

Display

How to open a new workbook every time the macro executes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions