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

 

[ItalianPlatinum]

Okay hitting a roadblock when I select the file it is trying to open in the tab the button is. Even though I am activating the sheet within the code

Sheets("TXT").Activate
Range("A:Z").Select
Selection.ClearContents
Range("A1").Select

OpenCopyTXT (this is a sub of the above, also put it below)

Sub OpenCopyTXT()
Dim Fname As String
With Application.FileDialog(3)
.InitialFileName = "MY PATH"
.Filters.Add "Text Files Only", "*.txt"
If .Show = -1 Then Fname = .SelectedItems(1)
End With
End Sub

 

[Fluff]

There is nothing there that opens the file, it only gets the file name.

 

[ItalianPlatinum]

So i was going to give the user an option to open the file through the dialogbox. My concern with pegging the file name is that the file name will change each day with the time it was saved at the end. Is there a way for me to import the file if a certain criteria is met so if file name is "AAA_20200529_0515" and only care about the is met "AAA_20200529"i guess that is one way i could work around it?

 

[Fluff]

You can use the code to allow the user to select the correct & then use that filename to open it.
Depending on what type of text file it is you might be able to use

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
   Workbooks.OpenText Fname
End Sub

 

[ItalianPlatinum]

Thank you Fluff so that actually worked. And if i wanted to remove the option to select via a dialog box and force the file to open using a string of variables that are met in the file name is that doable?

AAA_20200529_0515

 

[Fluff]

Yes that's doable, but you will need to start a new thread, as it's a different problem.

 

[ItalianPlatinum]

Fully understand. Thank you so much for helping me.

 

[Fluff]

You're welcome.