excelvbacsvtxt

Using Excel VBA (Name statement) to change file extension from .txt to .csv, getting Run-time error '75': Path/File access error


Hello and thank you in advance for any advice:

I need to use Excel VBA to save an .xlsx file as a .txt file, then convert that .txt file's extension to .csv for a sweep job that can only detect .csv files. I'm attempting to change the extension using the Name statement but am getting "Run-time error '75': Path/File access error" with the code below. Can anyone tell me why, please? I get the same error whether or not I have file extensions displayed in Windows Explorer.

    Dim Ext_M As String
    Dim Ext_X As String
    Dim Ext_T As String
    Dim Ext_C As String
    Dim Upload_File_Folder As String
    Dim Upload_File_Name As String
    Dim Upload_File_Path_M As String
    Dim Upload_File_Path_X As String
    Dim Upload_File_Path_T As String
    Dim Upload_File_Path_C As String
    Dim Upload_File_wb_M As Workbook
    Dim Upload_File_wb_X As Workbook
    Dim Upload_File_ws As Worksheet
    Ext_M = ".xlsm"
    Ext_X = ".xlsx"
    Ext_T = ".txt"
    Ext_C = ".csv"
    Upload_File_Folder = 'using a Public Const here and can confirm that it is not missing a backslash
    Upload_File_Name = "File_Name_" & Format(Now(), "yyyymmdd")
    Upload_File_Path_M = Upload_File_Folder & Upload_File_Name & Ext_M
    Upload_File_Path_X = Upload_File_Folder & Upload_File_Name & Ext_X
    Upload_File_Path_T = Upload_File_Folder & Upload_File_Name & Ext_T
    Upload_File_Path_C = Upload_File_Folder & Upload_File_Name & Ext_C
    ThisWorkbook.SaveCopyAs (Upload_File_Path_M)
    Set Upload_File_wb_M = Workbooks.Open(Upload_File_Path_M)
    Upload_File_wb_M.SaveAs Filename:=Upload_File_Path_X, FileFormat:=xlOpenXMLWorkbook
    Set Upload_File_wb_X = Workbooks(Upload_File_Name & Ext_X)
    With Upload_File_wb_X
        With Worksheets("PurchaseData")
            Range("PurchaseData_Table[#All]").Copy
            Range("PurchaseData_Table[#All]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            .ListObjects(1).Unlist
        End With
        For Each Upload_File_ws In .Worksheets
            If Upload_File_ws.Name <> "PurchaseData" Then
                Upload_File_ws.Delete
            Else
                Upload_File_ws.Cells.EntireColumn.AutoFit
            End If
        Next Upload_File_ws
        Sheets("PurchaseData").Name = "Sheet1"
        .Save
        .SaveAs Filename:=Upload_File_Path_T, FileFormat:=xlTextWindows
    End With
    Kill (Upload_File_Path_M)
    Name Upload_File_Path_T As Upload_File_Path_C 'This is the line throwing the error.

Solution

  • You can't rename a file that's open in Excel.

             Sheets("PurchaseData").Name = "Sheet1"
            .Save
            .SaveAs Filename:=Upload_File_Path_T, FileFormat:=xlTextWindows
            .Close False '<<<<<
        End With
    

    Also in this With block you need to bind Range to the worksheet object

        With Worksheets("PurchaseData")
            .Range("PurchaseData_Table[#All]").Copy
            .Range("PurchaseData_Table[#All]").PasteSpecial Paste:=xlPasteValues, _
                           Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            .ListObjects(1).Unlist
        End With
    

    Might be a bit tidier as:

        With Worksheets("PurchaseData").ListObjects(1)
            .Range.Value = .Range.Value
            .Unlist
        End With