Stop dialog box

[paul.raeburn]

I have some code that does the following:

kills a spreadsheet if it exists,
opens a template,
save as a worksheet,
transfer data to it,
opens the spreadseet,
runs a macro
then I save it...

I am using the following to save the spreadsheet:

xlBook.SaveAs DirPath & xlsfname
xlBook.Close

I have two problems, one is that when I use xlBook.Save, I get a complie
error: wrong number of arguments or invalid property assignment...

the second is with SaveAs I get a excel dialog box stating "A file name
already exists etc...

how can I solve this

 

[rapsr59]

I don't know if I am on the right path to helping you. I've never done any coding in the scope that you require. The only coding I've done is in Access. However, I did try this.

To get a complete list of all parameters and arguments required by the Excel SaveAs cmd you might run a macro in a spread sheet. You can then open the macro for editing and view the complete parameters as designed by the macro. I did this and got...

ActiveWorkbook.SaveAs Filename:="C:\Users\Richard\Documents\Book3.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

You can then edit the code and set the required parameters and arguments as per your requirements.

Hope this helps you.

Richard

 

[rapsr59]

Paul R

Again I'm not sure if the following will help you as I am not familiar with your complete code but...

I was having trouble with "Kill"ing a file and replacing it with another file by the same name. The file was an Access file. I then came up with following so I could accomplish this.

It seemed that by Kill"ing and then trying to save the new file I was not giving the operating system sufficient time to clear the old file and then save the new one so...

I first saved the new file under a different name, then I "kill"ed the old file and then I renamed the new file.

Or...

You can rename the file you are going to kill, Save the new file using the existing file name, and then "Kill" the old renamed file.

Maybe ths help you with your project.

Have a very Happy New Year!


Richard

PS Just thought about this...

Dim OldName, NewName, PathName
    PathName = "C:\MYDIR\"               'Set the path
    OldName = PathName & "FILENAME"      'Name Of File To Be Deleted
    NewName = PathName & "FILENAMETEMP"  'Change name temporarily until deletion
    Name OldName As NewName              'Rename the file
 
    '--------------------------------------------------
    'Code goes here to save the new file under FILENAME
    '--------------------------------------------------
 
    'Delete the old file
    Kill NewName

 

[paul.raeburn]

many thanks all, I have manage to do this by using your sugesstions...

regards - Paul