CSV conversion to Excel

[Junior6202]

Hi all,

I'm fairly new to Vbs and I have a script that takes a particular csv file and converts it to excel. The problem is that the cvs file is automated and it always have a different name. I'm setting this script to run automatically so is there a way to set the .InitialFileName to pick every file .csv as opposed to a particular name. Thanks in advance.



Sub SaveAsXLSX()

Dim wbSource As Workbook
Dim vrtSelectedItem As Object

'Allows you to pick the CSV file from wherever it's been saved.
With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "P:\test\QVD CoreCentric140306_000136011.csv"
.AllowMultiSelect = False
.Show
For Each vrtSelectedItem In .SelectedItems
wbSource = Workbooks.Open(vrtSelectedItem)
Next
End With

'Saves the file as an .xlsx file.
wbSource.SaveAs(Filename = "QVD_UPS.xlsx", FileFormat:=51)

End Sub

 

[SkipVought]

Hi,

If you're using vb script, where is your code to define the Excel application object & the Excel workbook object???

 

[Junior6202]

Thank you for the Response SkipVought but I figured it out. I just used a wildcard in the .InitialFileName = "P:\test\*.csv".

 

[SkipVought]

BTW, I strongly recommend against opening a text file directly via the Workbook.Open method, because Excel can and will change certain data irrevocably.

The method that I would recommend is to open a new workbook and IMPORT the text data via Data > Get external data > From Text file...

While performing such an import, the data type for each data column can be specified, so that your data will appear in the workbook correctly.