VBA - dialog box, open txt and copy/paste in excel

[ItalianPlatinum]

Have considerable amount of issues trying to get my VBA to even start. in quick summary was looking to:

1) A button to allow user a dialog box to select a .txt file (defaulting the folder path)
2) Clear contents of a tab in an existing workbook where the .txt file will be copied and paste it into
3) Formatting the data to remove any blanks rows between the data

I am not overly concerned with 2 & 3. #1 is where i am struggling. every time i use msoFileDialogFilePicker or msoFileDialogOpen it is giving me a compile error.

Dim intChoice As Integer

Application.FileDialog(msoFileDialogOpen).InitialFileName = "MY PATH"
intChoice = Application.FileDialog(msoFileDialogOpen).Show
If intChoice <> 0 Then
Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _"Text Files Only", "*.txt")
End If
End Sub

 

[Fluff]

How about

Sub ItalianPlatinum()
   Dim Fname As String
   With Application.FileDialog(3)
      .InitialFileName = "C:\mrexcel\text & csv\"
      .Filters.Add "Text Files Only", "*.txt"
      If .Show = -1 Then Fname = .SelectedItems(1)
   End With
End Sub

 

[Norie]

Do you want the user to select a file or open a file?

 

[ItalianPlatinum]

How about

Sub ItalianPlatinum()
   Dim Fname As String
   With Application.FileDialog(3)
      .InitialFileName = "C:\mrexcel\text & csv\"
      .Filters.Add "Text Files Only", "*.txt"
      If .Show = -1 Then Fname = .SelectedItems(1)
   End With
End Sub

How do I get it to default to a folder path on the dialogbox?

 

[ItalianPlatinum]

Do you want the user to select a file or open a file?

i want the user to select the file or i guess open it is fine and then do 2 and 3.

 

[Fluff]

Just change this to your path

.InitialFileName = "C:\mrexcel\text & csv\"

 

[ItalianPlatinum]

Just change this to your path

.InitialFileName = "C:\mrexcel\text & csv\"

which i did but not defaulting it to that path

 

[Fluff]

That suggests that the path name you used is not valid.

 

[ItalianPlatinum]

So it works if i remove this from the end text & csv\"

 

[Fluff]

Glad it's sorted & thanks for the feedback.