Save as, as different file type

breakhappy

New Member
Joined
Jun 30, 2008
Messages
22
First off, I would like to say that I am not an experienced programmer and am just learning VB. I have no training and just try to strong together code based on logic. Most of what I've learned so far has been from forums like this one and am hoping someone here can help me. I appreciate any advice or help anyone can give me...


Based on the selection of radio buttons on a userform I created, I am looking for a sub that will 'Save As', as a different file type. Let me first describe the userform objects and then what I am trying to do.

The userform will be used for exporting data from one worksheet to another. The userform has 3 radio buttons to allow the user to save the data in different file formats. The first - .dbf format, the second - .txt format, the third - .csv format. The userform also has a textbox that allows the user to enter a name in which they would like the exported file to be named.

I am trying to string together a macro that will:
1. Identify which radio button the user has selected
2. Take the text that the user has filled in the userbox (show msgbox error if no text has been entered) and save the file as the appropriate file format type, based on which radio button has been selected.

This is the code that I had but it doesn't seem to be working (Ignore it if you think I'm going about it all wrong).
Code:
Sub SaveAsCell()
Dim strName As String
On Error GoTo InvalidName
    If DBFOptionButton = True Then
        strName = SaveFileName.Value & Sheet5.Range("E2")
        ActiveWorkbook.SaveAs strName
        FileFormat = xlDBF4
    Else
    GoTo Next_Button
    
Next_Button:
    If ASCIIOptionButton = True Then
            strName = SaveFileName.Value & Sheet5.Range("E3")
            ActiveWorkbook.SaveAs strName
            FileFormat = xlText
    Else
    GoTo Next_Button2
Next_Button2:
    If CommaOptionButton = True Then
            strName = SaveFileName.Value & Sheet5.Range("E2")
            ActiveWorkbook.SaveAs strName
            FileFormat = xlCSV
    Else
    GoTo InvalidName
        
    End If
    
    
Exit Sub
InvalidName: MsgBox "The text: " & strName & _
        " is not a valid file name."
 
End Sub [\code]
 
The error code that I recieve is: "Compile error: Block If without End If". Can anyone help?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

decklun

Board Regular
Joined
Dec 4, 2006
Messages
126
Hi Breakhappy,

Welcome to the board.

You are missing 2 "end if" lines. Remember, each time you have an "IF" you have to have an "END IF" Your code should look like this....

Code:
Dim strName As String
On Error GoTo InvalidName

If DBFOptionButton = True Then
    strName = SaveFileName.Value & Sheet5.Range("E2")
    ActiveWorkbook.SaveAs strName
    FileFormat = xlDBF4
        Else
        GoTo Next_Button
End If


Next_Button:

If ASCIIOptionButton = True Then
    strName = SaveFileName.Value & Sheet5.Range("E3")
    ActiveWorkbook.SaveAs strName
    FileFormat = xlText
        Else
        GoTo Next_Button2
End If

Next_Button2:

If CommaOptionButton = True Then
    strName = SaveFileName.Value & Sheet5.Range("E2")
    ActiveWorkbook.SaveAs strName
    FileFormat = xlCSV
        Else
        GoTo InvalidName
End If


Exit Sub
InvalidName: MsgBox "The text: " & strName & _
" is not a valid file name."

I have not tested the code, but this should take care of the "Compile error: Block If without End If" error.
 

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
You just left out a couple if End If sttements. Try this:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> strName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> InvalidName<br><SPAN style="color:#00007F">If</SPAN> DBFOptionButton = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>strName = SaveFileName.Value & Sheet5.Range("E2")<br>ActiveWorkbook.SaveAs strName<br>FileFormat = xlDBF4<br><SPAN style="color:#00007F">Else</SPAN><br><SPAN style="color:#00007F">GoTo</SPAN> Next_Button<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>Next_Button:<br><SPAN style="color:#00007F">If</SPAN> ASCIIOptionButton = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>strName = SaveFileName.Value & Sheet5.Range("E3")<br>ActiveWorkbook.SaveAs strName<br>FileFormat = xlText<br><SPAN style="color:#00007F">Else</SPAN><br><SPAN style="color:#00007F">GoTo</SPAN> Next_Button2<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>Next_Button2:<br><SPAN style="color:#00007F">If</SPAN> CommaOptionButton = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>strName = SaveFileName.Value & Sheet5.Range("E2")<br>ActiveWorkbook.SaveAs strName<br>FileFormat = xlCSV<br><SPAN style="color:#00007F">Else</SPAN><br><SPAN style="color:#00007F">GoTo</SPAN> InvalidName<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><br><SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top