Find file and remove .csv extension

[Rob]

Hi,

I'm tring to open a window, select a file with CSV extension, the file is
coded to SelectedFile and then remove I want to remove the .CSV before
renaming the file with the same name albeit with a .TXT extension.

I've tried the code below for the inital test but it fails on the
WorksheetFunction code.

Am I on the right track?

Thanks, Rob
Sub macro1()

Dim Filter, Caption, SelectedFile, DestinationFile As String

Filter = "Text files (*.csv),*.csv"
Caption = "Please Select a File " & TheUser
SelectedFile = Application.GetOpenFilename(Filter, , Caption)

SelectedFile = Application.WorksheetFunction _
.Left(SelectedFile, Find(".CSV", (.Upper(SelectedFile)), 1) - 1)

Debug.Print SelectedFile

End Sub

 

[Peter T]

If I follow ...

'Option Explicit
Sub Test()
Dim Filter As String, Caption As String, DestinationFile As String
Dim SelectedFile As Variant

Filter = "Text files (*.csv),*.csv"
Caption = "Please Select a File " '' & TheUser ''< ?
SelectedFile = Application.GetOpenFilename(Filter, , Caption)
If VarType(SelectedFile) = vbBoolean Then
MsgBox "user cancelled"
Else
On Error Resume Next
DestinationFile = SelectedFile
Mid(DestinationFile, Len(DestinationFile) - 2, 3) = "txt"
Debug.Print SelectedFile
Debug.Print DestinationFile
Name SelectedFile As DestinationFile
If Err.Number Then
MsgBox Err.Description, , "Error renaming file"
End If
On Error GoTo 0
End If
End Sub


Regards,
Peter T

 

[Dave Peterson]

VBA has its own equivalent of =Upper(), =Find(), =left(), so you don't need to
use the worksheetfunctions:

Option Explicit
Sub macro1()

Dim myFilter As String
Dim myCaption As String
Dim SelectedFile As Variant 'could be false if user cancels
Dim NewName As String
Dim TheUser As String

TheUser = "hi there"

myFilter = "Text files,*.csv"
myCaption = "Please Select a File " & TheUser

SelectedFile = Application.GetOpenFilename(fileFilter:=myFilter, _
Title:=myCaption)

If SelectedFile = False Then
Exit Sub 'user hit cancel
End If

If UCase(Right(SelectedFile, 4)) = UCase(".csv") Then
NewName = Left(SelectedFile, Len(SelectedFile) - 4) & ".txt"
Name SelectedFile As NewName
End If

End Sub

============
When I'm doing this kind of thing, I'll usually just copy the file to a .txt,
open it, copy it to where I want, close the text workbook, and delete the .txt
file.

That way, I don't have to worry about modifying that .csv file.

Option Explicit
Sub macro1()

Dim myFilter As String
Dim myCaption As String
Dim SelectedFile As Variant 'could be false if user cancels
Dim NewName As String
Dim TheUser As String
Dim DestCell As Range
Dim TempWks As Worksheet

myFilter = "Text files,*.csv"
myCaption = "Please Select a File " & TheUser

SelectedFile = Application.GetOpenFilename(fileFilter:=myFilter, _
Title:=myCaption)

If SelectedFile = False Then
Exit Sub 'user hit cancel
End If

Set DestCell = Worksheets.Add.Range("A1")

NewName = SelectedFile & ".txt"
FileCopy Source:=SelectedFile, Destination:=NewName

Workbooks.OpenText Filename:=NewName 'and all the other parsing stuff here

Set TempWks = ActiveSheet 'the worksheet in the new text workbook

TempWks.Cells.Copy _
Destination:=DestCell

TempWks.Parent.Close savechanges:=False

Kill NewName

End Sub

 

[Rob]

Thank you both, two solutions I will work with to reach my objective.

Many thanks for the prompt reply. Rob