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