Prompt User for Filename (Last Time)

[crobertson]

Ok,

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( _
    fileFilter:="CSV (*.csv), *.csv")
    If Fname = False Then
    MsgBox "You didn't enter a filename!"
    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

This is working great, but here is my dilema, I now have to get a selection from a combo box + user input for the filename.

Any help is greatly appreciated!

 

[Norie]

What's the actual question?

Do you want to combine the value from the combobox with the value from GetSaveAsFileName?

 

[crobertson]

Yes, I'm sorry I started the post and then did something else and posted it.

I need to take the value selected from say CombBox1, and then make it the default value when the user is prompted for a filename, so they can add the job number for the filename.

Does that make sense?

 

[Norie]

Take a look at the InitialFilename argument of GetSaveAsFilename.

Fname = Application.GetSaveAsFilename( InitialFileName = Combobox1.Value,  _ 
    FileFilter:="CSV (*.csv), *.csv")

 

[crobertson]

Norie,


Thank you so much, I am so close to having it perfect, except the Filename defaults to FALSE not what is in my combobox. Here is the code from my user form.

Private Sub ComboBox99_Change()

End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub CommandButton1_Click()
EraseData
findfile
End Sub

Private Sub CommandButton2_Click()
X
End Sub

Private Sub Image1_Click()

End Sub

Private Sub Label1_Click()

End Sub

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(InitialFileName = ComboBox1.Value, _
    FileFilter:="CSV (*.csv), *.csv")
    If Fname = False Then
    MsgBox "You didn't enter a filename!"
    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





Private Sub UserForm_Click()

End Sub

Any help is greatly appreciated!

 

[crobertson]

How about this...

What if I were to add a text box to my user form, and make the file name the value of ComboBox1 & TextBox1?

That would make things simpler for me and my users.

How would I get that information as the filename?

Any and all help is greatly appreciated!

 

[crobertson]

Ok, Ok, I got it now!

My filename is coming out with the initial file name of my ComboBox1.Value, now the final question is how can I get it to add to that the value of TextBox1

I really appreciate your help!

MsgBox "Please enter the job number as the filename."
    Fname = Application.GetSaveAsFilename(InitialFileName:=ComboBox1.Value, _
    FileFilter:="CSV (*.csv), *.csv")
    If Fname = False Then
    MsgBox "You didn't enter a filename!"
    End If

 

[crobertson]

How can I get the file name to be ComboBox1.Value & TextBox1.Value


Thank you in advance for your help!

 

[Norie]

Do you mean the initial filename?

 Fname = Application.GetSaveAsFilename(InitialFileName:=ComboBox1.Value & Textbox1.Value, _ 
    FileFilter:="CSV (*.csv), *.csv")

 

[crobertson]

I knew it was going to be simple!

I added an underscore to make it easier for the 3rd party program to import my file.

Fname = Application.GetSaveAsFilename(InitialFileName:=ComboBox1.Value & "_" & TextBox1.Value, _
    FileFilter:="CSV (*.csv), *.csv")

Norie,

Thank you so much for your help!


crobertson[/code]