Macro to open a file open dialog box

[C C]

Hello.

I want to insert a File open dialog box in an existing macro. How do I
accomplish this?

The file I want to open and run the macro on is a .TXT file. And at the end
of my macro, I want to save it in "C:\My Documents" folder as an Excel
workbook with the filename (without the .txt extension) plus date plus, of
course, the .xls extension.

Thanks in advance.

 

[Dave Peterson]

something like:

Option Explicit
Sub testme()

Dim myFileName As Variant
Dim Wkbk As Workbook

myFileName = Application.GetOpenFilename("Text files, *.txt")
If myFileName = False Then
Exit Sub 'user hit cancel
End If

Workbooks.OpenText Filename:=myFileName ', ...rest of your macro

Set Wkbk = ActiveWorkbook

'do more things

'fix the name to save
myFileName = Wkbk.Name 'stip off drive/path
If LCase(Right(myFileName, 4)) = ".txt" Then
myFileName = Left(myFileName, Len(myFileName) - 4)
End If

myFileName = "C:\my Documents\" & myFileName & "_" _
& Format(Date, "yyyy_mm_dd") & ".xls"

Application.DisplayAlerts = False
Wkbk.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

End Sub

 

[C C]

Dave.

Thanks so much. This will help me my opening and saving routines in my
macro.

Cheers.