PDA

View Full Version : [SOLVED] How to rename the .XL file with the name of the .CSV file



Jagdev
05-19-2015, 03:45 AM
Hi Experts

I have the below code with converts the .CSV file to .XL file. I want to add additional functionality in it. I want the name of the new .XL converted file name same as that of the .CSV file from which the data is converted to .XL.


Sub CsvInput()
Dim dim1 As Integer, dim2 As Integer, fileNum As Integer
Dim delim As String
Dim strCSV As String
Dim fd As FileDialog
Dim initArray As Variant, finArray As Variant, selectedFile As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fileNum = 1
delim = InputBox(Prompt:="Delimiter Selection", Title:="Please enter your required delimiter", Default:=",")
Set ws = ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheet s.Count))
ws.Name = "Temp"

With fd
If .Show = -1 Then

For Each selectedFile In .SelectedItems
strCSV = ImportTextFile(selectedFile)
initArray = Split(strCSV, vbCrLf)
dim1 = UBound(initArray)
dim2 = UBound(Split(initArray(0), delim))
ReDim finArray(dim1, dim2)

For Idx1 = LBound(initArray) To UBound(initArray) - 1

For Idx2 = 0 To dim2
finArray(Idx1, Idx2) = Split(initArray(Idx1), delim)(Idx2)
Next
Next

Sheets("Temp").Range("A" & fileNum).Resize(UBound(finArray), UBound(Application.Transpose(finArray))) = finArray
fileNum = fileNum + 100
Next selectedFile
End If
End With

Sheets("Temp").Select
Sheets("Temp").Move
Sheets("Temp").Name = "Final Output"
End Sub


Function ImportTextFile(strFile As Variant) As String
Open strFile For Input As #1
ImportTextFile = Input$(LOF(1), 1)
Close #1
End Function

SamT
05-19-2015, 07:43 AM
I want the name of the new .XL converted file name same as that of the .CSV file from which the data is converted to .XL.

you are importing multiple .CSV's into one Excel File. Which .CSV name do you want to use?

If you are only opening one .CSV, then set AllowMultiSelect=False, and grab the SelectedFile.Name. Strip the ".csv" and SaveAs

Jagdev
05-19-2015, 08:17 AM
Hi Sam

I am importing one at a time .CSV to XL with the above code. I want the new file to be renamed with its CVS file name. I tried using SelectedFile.Name code, but getting following error msg. "Can't assign to read only property.

ActiveWorkbook.Name = selectedFile.Name

Regards,
JD

SamT
05-19-2015, 05:20 PM
If you put the words "Option Explicit" at the top line of your code page, you will find some "Best Practices" errors. IF you look at the bottom code (a Function) you will see a curious thing.

The following edits and Function all compile together, but I have not tested them

After
With fd add
.AllowMultiSelect = False

Change
For Each selectedFile in .SelectedItems to
selectedFile = .SelectedItems(1) and remove
Next selectedFile



After
Sheets("Temp").Name = "Final Output"
Add
ActiveWorkbook.SaveAs(CleanName(selectedFile)



Add This function
Private Function CleanName(Longname) As String
Dim PositNameStart As Long
Dim PositDot As Long
Dim NameLength As Long

PositDot = InStr(Longname, ".")
PositNameStart = InStrRev(Longname, "\") + 1
NameLength = PositDot - PositNameStart

CleanName = Mid(Longname, PositNameStart, NameLength)

End Function

Jagdev
05-20-2015, 02:17 AM
Hi Sam

This is fantastic! It is renaming the excel file.

Is it possible say I place all the .CSV files in a folder and the code pull each file, convert it and rename it and save it back in the same folder.

Regards,
JD

snb
05-20-2015, 03:05 AM
Yes that is possible.

Jagdev
05-20-2015, 03:15 AM
Hi snb

Saving of file can be done with ActiveWorkbook.Save and ActiveWorkbook.Close functions. Could you please please help me with the code which will loop all the .CSV on a click and convert them to excel.

Regards,
JD

snb
05-20-2015, 03:41 AM
You've got enough help to create that code yourself.

SamT
05-20-2015, 06:28 AM
I just gave this code to another person yesterday.


FoundName = Dir(SubPath & "\" & Cel.Value & ".*")

Do While FoundName <> "" 'Repeat search until all named files found
.Cells(NameRow, NameColumn) = SubPath & "\" & FoundName 'Put the path and name in a cell
NameColumn = NameColumn + 1 'Use next Column 'Next time use next Cell
FoundName = Dir
Loop



Here's the thread:VBA to search given cell value in given folder path (http://www.vbaexpress.com/forum/showthread.php?52625-VBA-to-search-given-cell-value-in-given-folder-path)

Jagdev
05-21-2015, 03:33 AM
Hi Sam

Thanks for the code and help you provided in the aforementioned thread.

Regards.
JD

SamT
05-21-2015, 07:46 AM
Was that enough for you to finish the project?

Jagdev
05-21-2015, 09:55 AM
Hi Sam

I managed to get the work done. Thanks for your guidance and support.

Regards,
JD

SamT
05-21-2015, 10:27 AM
It's always a pleasure to work with someone willing to help themselves.

SamT

Jagdev
05-22-2015, 06:25 AM
Thanks Sam!