Prompt to save existing file or create new file

Rabbitoh
07-04-2010, 08:00 PM
I need some VBA code that will allow me to do the following

1. I run some code that is preset to open a workbook named “myworkbook.xls”
2. If it exists then it opens
3. However if it does not exist, then it presents a response box saying “myworkbook.xls does not exist. Create it now?"
4. If no selected then the routine ends
5. If yes selected, then a file named template.xls is opened and it then auto-saves to the said filename myworkbook.xls

This the code I have been offered by ajetrumpet but cannot get to work:

Sub routine()

Dim mypath As String
Dim temppath As String
Dim xlfile As Excel.Workbook

mypath = "c:\myfolder\myworkbook.xls"
temppath = "c:\myfolder\template.xls"

If Dir(mypath) > "" Then
Set xlfile = Workbooks.Open(mypath)
Set xlfile = Nothing
Me.Close
Exit Sub
Else
If MsgBox("Files does not exist" & vbCr & _
"Create it now?", vbYesNo) = vbNo Then
Exit Sub
Else
Set xlfile = Workbooks.Open(temppath)
xlfile.SaveAs Filename:=mypath, FileFormat:=xlExcel8
Me.Close
Exit Sub
End If
End If

End Sub

Rabbitoh
07-04-2010, 09:57 PM
Got it working now. Removed the Me.Close. This code is excellent and useful for others who want to do the same thing. All power to ajetrumpet

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum