Sub CopyCSVtoXLSX()
Dim sHomeDir As String
sHomeDir = CurDir
Dim vTemp As Variant
vTemp = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File", MultiSelect:=False)
If TypeName(vTemp) = "Boolean" Then
' False, so canceled
GoTo ExitSub
End If
Dim CSVFilePathName As String
CSVFilePathName = vTemp
Dim FileNameRoot As String
FileNameRoot = Mid$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) + 1)
FileNameRoot = Left$(FileNameRoot, InStr(FileNameRoot, ".") - 1)
Dim XLSXFilePath As String
' if it's always the same
XLSXFilePath = "C:\otherpath"
' if it is different, let's start with CSV path
XLSXFilePath = Left$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) - 1)
Dim XLSXFilePathName As String
XLSXFilePathName = XLSXFilePath & Application.PathSeparator & FileNameRoot & ".xlsx"
vTemp = Application.GetSaveAsFilename(FileFilter:="Excel Workbooks (*.xlsx), *.xlsx", InitialFileName:=XLSXFilePathName, Title:="Select Workbook Name and Path")
If TypeName(vTemp) = "Boolean" Then
' False, so canceled
GoTo ExitSub
End If
Dim wb As Workbook
Set wb = Workbooks.Open(CSVFilePathName)
wb.SaveAs XLSXFilePathName, xlWorkbookDefault
Kill CSVFilePathName
ExitSub:
ChDrive sHomeDir
ChDir sHomeDir
End Sub