Macro to open a file open dialog box

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.

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 wrote:
>
> 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

Dave.

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

Cheers.

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:44D3409D.D1BF18BA@verizonXSPAM.net...
> 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 wrote:
>>
>> 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