Removing specific text while saving the exported excel into pdf

[Rajsngh]

I am converting CSV into XLS and then doing text to column and then trying to export the active sheet as pdf , but while exporting pdf the file name should be the CSV file name but below code is giving the name like "CSV file name " along with ".CSV" extention
And also I am not able to do fit to column and fit all rows in one page and page orientation as landscape
Code is below and lots of people have helped in developing it including mr. Fluff

Sub CSVtoXLS()



Dim xFd As FileDialog

Dim xSPath As String

Dim xCSVFile As String

Dim xWsheet As String

Application.DisplayAlerts = False

Application.StatusBar = True

xWsheet = ActiveWorkbook.Name

Set xFd = Application.FileDialog(msoFileDialogFolderPicker)

xFd.Title = "Select a folder:"

If xFd.Show = -1 Then

xSPath = xFd.SelectedItems(1)

Else

Exit Sub

End If

If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"

xCSVFile = Dir(xSPath & "*.csv")

Do While xCSVFile <> ""

Application.StatusBar = "Converting: " & xCSVFile

Workbooks.Open Filename:=xSPath & xCSVFile



Columns("A:A").Select



Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _

Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _

), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _

(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _

Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1)), TrailingMinusNumbers:=True





Dim i As Long

Dim hdr As Range

Set hdr = Range("A1:R1")

For i = Range("C" & Rows.Count).End(xlUp).Row To 3 Step -1

If Cells(i, 3) <> Cells(i - 1, 3) Then

Cells(i, 1).Resize(, 18).Insert

Cells(i, 1).Resize(, 18).Value = hdr.Value

End If

Next i

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:=(xSPath & xCSVFile) & ".pdf", _

Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=True



'ActiveWorkbook.Close

Windows(xWsheet).Activate

xCSVFile = Dir



Loop

MsgBox "conversion done"

Application.StatusBar = False

Application.DisplayAlerts = True







End Sub

 

[Rajsngh]

Was able to remove CSV file extention by doing left(filename,len(filename)-4)
But help in setting the layout as landscape and fit to colum still required