Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CSV conversion to Excel

Status
Not open for further replies.

Junior6202

Programmer
Sep 19, 2014
7
US
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
 
Hi,

If you're using vb script, where is your code to define the Excel application object & the Excel workbook object???
 
Thank you for the Response SkipVought but I figured it out. I just used a wildcard in the .InitialFileName = "P:\test\*.csv".[bigsmile]
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top