Save CSV As XLSX with Same Name & Directory

[Philip1957]

Greetings,
I have the following code which converts list data into tabular data that can be used as a lookup table. This macro resides in my Personal.xlsb. Everything works fine except for the final save as sub. It wants to save the file in my OneDrive root rather than the directory where the original csv file is located. This directory can change so I can't specify a fixed path for the saved file. If there is no save location specified, shouldn't it default to the path where the original file is located?

Option Explicit

Sub Ref_Desig_Tab()

Application.ScreenUpdating = False

'-----START TIMER-----
Dim StartTime As Double
Dim TimeTaken As String
StartTime = Timer

File_Prep
Transpose
Save_As_XLSX

'------ END TIMER------
TimeTaken = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Running time was " & TimeTaken & " (hours, minutes, seconds)"

Application.ScreenUpdating = True

End Sub

Private Sub File_Prep()

    ActiveSheet.Name = "Orig"
    ActiveSheet.Range("D:D").Copy Range("I:I")
    ActiveSheet.Range("F:F").Copy Range("J:J")
    ActiveSheet.Range("C:C").Copy Range("K:K")
    
    'Remove Spaces
    Columns("K:K").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        
    ' Text to Columns
    Columns("K:K").Select
    Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
        True
    
    Sheets.Add.Name = "Tabular"
    Worksheets("Tabular").Activate
    Range("A1").Value = "Ref Desig"
    Range("B1").Value = "P/N"
    Range("C1").Value = "Desc"
    Range("A1:C1").Font.Bold = True
    Range("A1:C1").HorizontalAlignment = xlCenter
    
    
End Sub  'File_Prep
Private Sub Transpose()
Dim copysheet As Worksheet
Dim pastesheet As Worksheet
Dim NumRows As Variant
Dim rw As Long
Dim lCol As Long
Dim lrowa As Long

rw = 2
Set copysheet = Worksheets("Orig")
Set pastesheet = Worksheets("Tabular")


    copysheet.Activate
    

    ' Set numrows = number of rows of data.
    NumRows = copysheet.UsedRange.Rows.Count

    ' Establish "For" loop to loop "numrows" number of times.
    For rw = 2 To NumRows

     'Copy & Paste Ref Desig Transposed
    lCol = Cells(rw, Columns.Count).End(xlToLeft).Column
    copysheet.Range(Cells(rw, 11), Cells(rw, lCol)).Copy
    pastesheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial , Transpose:=True
    Application.CutCopyMode = False

    ' Copy & paste P/N & description.
    copysheet.Range(Cells(rw, 9), Cells(rw, 10)).Copy
    pastesheet.Activate
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    lrowa = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Range(ActiveCell, Range("A" & Rows.Count).End(xlUp).Offset(, 1)).Resize(, 2).FillDown
    
    
    copysheet.Activate
    
    
    rw = rw + 1
    
    Next

End Sub  'Transpose

Private Sub Save_As_XLSX()

    ActiveWorkbook.SaveAs FileFormat:=51

End Sub  'Save_As_XLSX

I'm probably missing something obvious or simple

Any assistance with this would be appreciated.

Thanks in advance,
~ Phil

 

[Jon Peltier]

All you need to do is specify the file path and file name:

ActiveWorkbook.SaveAs FileName:=<FilePath&FileName> FileFormat:=51

 

[Philip1957]

Jon,

Thanks for the reply but the path for the Save As will vary. How do I make it use the path to the current active directory?

 

[Philip1957]

Jon,

Working with your reply I've come up with this

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & strName, FileFormat:=51

This is saving into the correct directory but not as .xlsx,. It remains a .csv file.

 

[Joe4]

That is because "FileFormat:=51" is an Excel format, not a CSV one.
You need something like: "FileFormat:=6" for CSV.

See here for the valid options: XlFileFormat enumeration (Excel)

 

[Jon Peltier]

Jon,

Working with your reply I've come up with this

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & strName, FileFormat:=51

This is saving into the correct directory but not as .xlsx,. It remains a .csv file.

I don't know why it's not saving as an Excel workbook. It works as advertized for me, on two different computers.

 

[Joe4]

I don't know why it's not saving as an Excel workbook. It works as advertized for me, on two different computers.

Whoops, I totally misread that last question, and got it backwards. Sorry about that.

How is "strName" defined/set?
You may actually be saving it as an Excel file, but with a "CSV" extension.
You need to make sure that your "strName" variable does not have a ".csv" extension at the end of it.

 

[Philip1957]

Here's the whole sub I have now.

Private Sub Save_As_XLSX()
Dim strName As String
Dim strPath As String
'Application.DisplayAlerts = False

strName = ActiveWorkbook.Name
strPath = ActiveWorkbook.Path


ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & strName, FileFormat:=51

End Sub  'Save_As_XLSX

You were correct, I can see in the locals window that the variable strName does include the .csv extension. Unless there is a separate object name for the file without the extension I guess I have to trim the last 4 characters from strName.

 

[Joe4]

If you change this line:

strName = ActiveWorkbook.Name

to this:

strName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name)-3) & ".xlsx"

it should do what you want.

 

[Jon Peltier]

Joe4 has a good point. If you manually try to save a file named "filename.csv" as an xlsx file, Excel will rename it "filename.csv.xlsx". But if you save it as "filename.csv" using the file format of a regular workbook, Excel will not append the xlsx extension.

If you try to open a regular workbook that has an unexpected extension (like csv), you will get a warning that the file format and extension do not match.