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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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,028

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,028
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,827
Messages
5,574,533
Members
412,601
Latest member
TheBeaniacExpress
Top