Prompt user for filename

crobertson

New Member
Joined
Nov 14, 2005
Messages
32
Ok, here is what I have and this works great, but what I need to do is prompt the user for the file name instead of it overwriting the same file.

Any help is greatly appreciated!

Sub X()
SaveAsDelimited "c:\test.csv", Range("'MAS90 Data'!A1:B4"), ","
End Sub

Sub SaveAsDelimited(FileName As String, Data As Range, Delimiter As String)
Dim strBuf As String
Dim rngTemp As Range
Dim rngCell As Range
Dim intUnit As Integer
Dim strDelimit As String
Dim strPath As String
Dim strName As String

intUnit = FreeFile
Open FileName For Output As intUnit
For Each rngTemp In Data.Rows
strBuf = ""
strDelimit = ""
For Each rngCell In rngTemp.Cells
If InStr(rngCell.Text, ",") > 0 Then
strBuf = strBuf & strDelimit & """" & rngCell.Text & """"
Else
strBuf = strBuf & strDelimit & rngCell.Text
End If
strDelimit = Delimiter
Next
Print #intUnit, strBuf
Next
Close intUnit
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
If I understand you correctly, check out XL's GetSaveAsFilename method.
 

crobertson

New Member
Joined
Nov 14, 2005
Messages
32
Thanks for the input, but I guess I am way to inexperienced. I don't know how I would incorporate that into the Sub?
 

crobertson

New Member
Joined
Nov 14, 2005
Messages
32
Ok, after some digging this is what I came up with, but how can I default the extension to .csv?

Code:
Sub X()
    SaveAsDelimited Range("'MAS90 Data'!A1:B4"), ","
 End Sub
 
Sub SaveAsDelimited(Data As Range, Delimiter As String)
    Dim Fname As Variant
    Dim strBuf As String
    Dim rngTemp As Range
    Dim rngCell As Range
    Dim intUnit As Integer
    Dim strDelimit As String
    Dim strPath As String
    Dim strName As String
    
    MsgBox "Please enter the job number as the filename."
    Fname = Application.GetSaveAsFilename()
    If Fname = False Then
    MsgBox "You didn't select a file"
    End If

    intUnit = FreeFile
    Open Fname For Output As intUnit
    For Each rngTemp In Data.Rows
        strBuf = ""
        strDelimit = ""
        For Each rngCell In rngTemp.Cells
            If InStr(rngCell.Text, ",") > 0 Then
                               strBuf = strBuf & strDelimit & """" & rngCell.Text & """"
            Else
                strBuf = strBuf & strDelimit & rngCell.Text
            End If
            strDelimit = Delimiter
        Next
        Print #intUnit, strBuf
    Next
    Close intUnit
End Sub
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

It's great that you figured this out on your own and thanks for formatting the code for readability.

Check out the arguments to the GetSaveAsFilename method, specifically the 2nd argument. It is called FileFilter. The help file explaining this argument also illustrates how it should be used.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
Two comments. You can move the MsgBox bit into the GetSaveAsFilename with
Code:
    'MsgBox "Please enter the job number as the filename."
    FName = Application.GetSaveAsFilename( _
        fileFilter:="CSV (*.csv), *.csv", _
        Title:="Please enter the job number as the filename.")
But, more important, if you know the job number is supposed to be the filename why ask the user for it? Don't you already know the job number?
 

crobertson

New Member
Joined
Nov 14, 2005
Messages
32
I do not know the job number, the user has to get the job number from another program.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,836
Messages
5,855,912
Members
431,774
Latest member
WillWilco

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