Save csv file as an xlsx file in a different location.

[Jungo]

I've see a few posts like this, but I need help with my specific set of needs:

I am struggling with syntax and language. I need a script that will run this set of logic:

-ACTIVEWORKBOOK.SAVEAS
-SAVE TO SPECIFIC DIRECTORY
-ORIGINAL FILE NAME NEEDS TO REMAIN, BUT CHANGE FROM CSV TO XLSX
-DELETE ORIGINAL

 

[Jon Peltier]

Here is the rudimentary code to do what you've asked.

Sub CopyCSVtoXLSX()
  Dim CSVFilePathName As String
  CSVFilePathName = "C:\somepath\Test1.csv"
  Dim FileNameRoot As String
  FileNameRoot = Mid$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) + 1)
  FileNameRoot = Left$(FileNameRoot, InStr(FileNameRoot, ".") - 1)
  Dim XLSXFilePath As String
  XLSXFilePath = "C:\otherpath"
  Dim XLSXFilePathName As String
  XLSXFilePathName = XLSXFilePath & Application.PathSeparator & FileNameRoot & ".xlsx"
  Dim wb As Workbook
  Set wb = Workbooks.Open(CSVFilePathName)
  wb.SaveAs XLSXFilePathName, xlWorkbookDefault
  Kill CSVFilePathName
End Sub

You can enhance it with GetOpenFileName to select the CSV file and GetSaveAsFileName to select the output folder and filename.

 

[Jungo]

Here is the rudimentary code to do what you've asked.

Sub CopyCSVtoXLSX()
  Dim CSVFilePathName As String
  CSVFilePathName = "C:\somepath\Test1.csv"
  Dim FileNameRoot As String
  FileNameRoot = Mid$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) + 1)
  FileNameRoot = Left$(FileNameRoot, InStr(FileNameRoot, ".") - 1)
  Dim XLSXFilePath As String
  XLSXFilePath = "C:\otherpath"
  Dim XLSXFilePathName As String
  XLSXFilePathName = XLSXFilePath & Application.PathSeparator & FileNameRoot & ".xlsx"
  Dim wb As Workbook
  Set wb = Workbooks.Open(CSVFilePathName)
  wb.SaveAs XLSXFilePathName, xlWorkbookDefault
  Kill CSVFilePathName
End Sub

You can enhance it with GetOpenFileName to select the CSV file and GetSaveAsFileName to select the output folder and filename.

Hey Jon,

thanks for this. i understand most of it but what would i change CSVFilePathName = "workbook.location" to, as the location that the csv file is always changing.

activeworkbook.location?

thanks!

 

[Jon Peltier]

If the CSV file is open and the active workbook, you could use

CSVFilePathName = ActiveWorkbook.FullName

I'm sure you don't want to keep changing the code to access the CSV file and path, so you could use GetOpenFileName to get the CSV file name and path and GetSaveAsFileName to get the XLSX file name and path.

Here's a routine which does both:

Sub CopyCSVtoXLSX()
  Dim sHomeDir As String
  sHomeDir = CurDir
  
  Dim vTemp As Variant
  vTemp = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File", MultiSelect:=False)
  If TypeName(vTemp) = "Boolean" Then
    ' False, so canceled
    GoTo ExitSub
  End If
  
  Dim CSVFilePathName As String
  CSVFilePathName = vTemp
  
  Dim FileNameRoot As String
  FileNameRoot = Mid$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) + 1)
  FileNameRoot = Left$(FileNameRoot, InStr(FileNameRoot, ".") - 1)
  
  Dim XLSXFilePath As String
  ' if it's always the same
  XLSXFilePath = "C:\otherpath"
  ' if it is different, let's start with CSV path
  XLSXFilePath = Left$(CSVFilePathName, InStrRev(CSVFilePathName, Application.PathSeparator) - 1)
  
  Dim XLSXFilePathName As String
  XLSXFilePathName = XLSXFilePath & Application.PathSeparator & FileNameRoot & ".xlsx"
  
  vTemp = Application.GetSaveAsFilename(FileFilter:="Excel Workbooks (*.xlsx), *.xlsx", InitialFileName:=XLSXFilePathName, Title:="Select Workbook Name and Path")
  If TypeName(vTemp) = "Boolean" Then
    ' False, so canceled
    GoTo ExitSub
  End If
  
  Dim wb As Workbook
  Set wb = Workbooks.Open(CSVFilePathName)
  wb.SaveAs XLSXFilePathName, xlWorkbookDefault
  Kill CSVFilePathName
  
ExitSub:
  ChDrive sHomeDir
  ChDir sHomeDir
End Sub