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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Thanks for the input, but I guess I am way to inexperienced. I don't know how I would incorporate that into the Sub?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,218,837
Messages
6,144,757
Members
450,567
Latest member
Mplz

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
Back
Top