Prompt user for filename

[crobertson]

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

 

[tusharm]

If I understand you correctly, check out XL's GetSaveAsFilename method.

 

[crobertson]

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]

Ok, after some digging this is what I came up with, but how can I default the extension to .csv?

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]

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.

 

[crobertson]

Thank you so much for your help! This is what I am using, referenced from the msdn article below.

MsgBox "Please enter the job number as the filename."
    Fname = Application.GetSaveAsFilename( _
    fileFilter:="CSV (*.csv), *.csv")
    If Fname = False Then
    MsgBox "You didn't select a file"
    End If

[url]http://msdn.microsoft.com/library/?url=/library/en-us/vbaxl10/html/xlmthGetSaveAsFilename.asp
[/url]

 

[tusharm]

Two comments. You can move the MsgBox bit into the GetSaveAsFilename with

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

I do not know the job number, the user has to get the job number from another program.