Prompt to create and save a file mid-routine (1 Viewer)

R

Rabbitoh

Registered User.

Local time

Today, 22:00

Joined

Jul 17, 2006

Messages

34

• Jun 22, 2010

• #1

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

If step 5 cannot be made to save to the myworkbook.xls automatically then I will accept that I will need to do it manually.

 

ajetrumpet

Banned

Local time

Today, 05:00

Joined

Jun 22, 2007

Messages

5,638

• Jun 22, 2010

• #2

PHP:Copy to clipboard

Sub routine()

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

mypath = "PATH\myworkbook.xls"
temppath = "PATH\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

 

R

Rabbitoh

Registered User.

Local time

Today, 22:00

Joined

Jul 17, 2006

Messages

34

• Jun 28, 2010

• #3

Nope, can't get this one to work. Code crashes out at the first Me.Close. All I have changed are the paths. they now point to the respective files as follows:

mypath = "c:\home\My Files\1111.xls"
temppath = "c:\home\My Files\template.xls"

Any ideas please.

 

R

Rabbitoh

Registered User.

Local time

Today, 22:00

Joined

Jul 17, 2006

Messages

34

• Jun 30, 2010

• #4

Nope, can't get this one to work. Code crashes out at the first Me.Close. All I have changed are the paths. they now point to the respective files as follows:

mypath = "c:\home\My Files\1111.xls"
temppath = "c:\home\My Files\template.xls"

Any ideas please.

 

chergh

blah

Local time

Today, 11:00

Joined

Jun 15, 2004

Messages

1,414

• Jul 1, 2010

• #5

have you tried removing me.close?

 

R

Rabbitoh

Registered User.

Local time

Today, 22:00

Joined

Jul 17, 2006

Messages

34

• Jul 5, 2010

• #6

Did that but still won't work.

 

R

Rabbitoh

Registered User.

Local time

Today, 22:00

Joined

Jul 17, 2006

Messages

34

• Jul 5, 2010

• #7

Got it working now. Me.Close also removed. Thanks