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
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